PostgreSQL session vs. TPQConnection vs. TSQLTransaction vs. TSQLQuery? lazarus / 16-Databases ---------------------------------------------------------------------- tatamata · 2016-06-13 19:09 With wich Lazarus/Freepascal object is PostgreSQL session related: TPQConnection, TSQLTransaction or TSQLQuery? For instance, PostgreSQL temporary tables or views are visible inside session only - what does it mean for SQLDb objects? Is a temporary table visible to a query, a transaction or a connection? ---------------------------------------------------------------------- sky_khan · 2016-06-14 01:51 AFAIK, Session = Connection ---------------------------------------------------------------------- goodname · 2016-06-14 12:59 This is really determined by the database engine and not the connection library. My understanding is that closing the transaction or the connection (which also closes the transaction) will close the temporary table or view state. So I guess the answer is that it is visible to the transaction. https://www.postgresql.org/docs/9.2/static/sql-createtable.html QuoteTEMPORARY or TEMP     If specified, the table is created as a temporary table. Temporary tables are automatically dropped at the end of a session, or optionally at the end of the current transaction (see ON COMMIT below). Existing permanent tables with the same name are not visible to the current session while the temporary table exists, unless they are referenced with schema-qualified names. Any indexes created on a temporary table are automatically temporary as well. Edit:  You might want to look up Transaction Isolation as well. That can have a impact on whether table updates are seen by other transactions for the view situation. ---------------------------------------------------------------------- LacaK · 2016-06-15 05:29 Plus each new transaction creates new connection. So two transactions are isolated as two separate connections. ---------------------------------------------------------------------- HeavyUser · 2016-06-15 05:36 Quote from: LacaK on June 15, 2016, 07:29:10 amPlus each new transaction creates new connection. So two transactions are isolated as two separate connections. Is that postgresql specific or the fpc data library? ---------------------------------------------------------------------- HeavyUser · 2016-06-15 06:26 Quote from: goodname on June 14, 2016, 02:59:19 pmThis is really determined by the database engine and not the connection library. My understanding is that closing the transaction or the connection (which also closes the transaction) will close the temporary table or view state. So I guess the answer is that it is visible to the transaction. No. A temporary table might be updated using multiple transactions and commands and kept in the server for as long as the application needs it. A session is usually connection bound ee the same application can not access the same session from multiple connections. ---------------------------------------------------------------------- marcov · 2016-06-15 12:16 Quote from: HeavyUser on June 15, 2016, 07:36:17 amQuote from: LacaK on June 15, 2016, 07:29:10 amPlus each new transaction creates new connection. So two transactions are isolated as two separate connections. Is that postgresql specific or the fpc data library? postgresql. ---------------------------------------------------------------------- goodname · 2016-06-15 12:20 Quote from: HeavyUser on June 15, 2016, 08:26:36 amNo. A temporary table might be updated using multiple transactions and commands and kept in the server for as long as the application needs it. A session is usually connection bound ee the same application can not access the same session from multiple connections. Well I was partially right. It is determined by the database engine and not the connection library and it can be transaction controlled but that is an option at table creation time. QuoteON COMMIT     The behavior of temporary tables at the end of a transaction block can be controlled using ON COMMIT. The three options are:     PRESERVE ROWS         No special action is taken at the ends of transactions. This is the default behavior.     DELETE ROWS         All rows in the temporary table will be deleted at the end of each transaction block. Essentially, an automatic TRUNCATE is done at each commit.     DROP         The temporary table will be dropped at the end of the current transaction block. ---------------------------------------------------------------------- goodname · 2016-06-15 13:03 Quote from: marcov on June 15, 2016, 02:16:51 pmQuote from: HeavyUser on June 15, 2016, 07:36:17 amQuote from: LacaK on June 15, 2016, 07:29:10 amPlus each new transaction creates new connection. So two transactions are isolated as two separate connections. Is that postgresql specific or the fpc data library? postgresql. I'm fairly sure the the pqconnection unit file now does connection pooling. It used to be that each new transaction created a new connection but now that there is pooling don't think this happens any more. Not sure if PostgreSQL connection pooling started in FPC 2.6.4 or 3.0. ---------------------------------------------------------------------- LacaK · 2016-06-15 17:20 Quote from: goodname on June 15, 2016, 03:03:45 pmQuote from: marcov on June 15, 2016, 02:16:51 pmQuote from: HeavyUser on June 15, 2016, 07:36:17 amQuote from: LacaK on June 15, 2016, 07:29:10 amPlus each new transaction creates new connection. So two transactions are isolated as two separate connections. Is that postgresql specific or the fpc data library? postgresql. I'm fairly sure the the pqconnection unit file now does connection pooling. It used to be that each new transaction created a new connection but now that there is pooling don't think this happens any more. Not sure if PostgreSQL connection pooling started in FPC 2.6.4 or 3.0. Yes new transaction reuses inactive connection in connection pool. Inactive means, that transaction, which previosly was using this connection was commited or rolledback. ---------------------------------------------------------------------- tatamata · 2016-12-02 13:05 What happens if connection to some remote PostgreSQL server is suddenly broken, just while a long-executing transaction (e.g. some long executing stored procedure) is running, triggered by TSQLQuery or TSQLScript? Will PostgreSQL server continue executing the started stored procedure (function) or PostgreSQL will stop the session? Is there any control over the process? Does KeepConnection property do any difference? ---------------------------------------------------------------------- tatamata · 2020-03-11 15:28 I still haven't figured out where Postgresql session is :-( For example, there is Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i select currval('some_sequence') in Postgresql, which is related to postgresql session. I couldn't find any combination of transactions and sqlqueries that would work with this statement. It seems as if each and every sqlquery is behaving as separate session... ---------------------------------------------------------------------- zeljko · 2020-03-11 16:56 It's pretty simple. Postgresql connection is session, so if SQLDB makes more connections then it's more sessions. Plastic example: 1.Open connection -> create temp table and fill with some data.   open transaction     you can use temptable here   end transaction (commit,rollback - whatever)     you  can use temp table here If you close this connection then temp table is vanished. 2.Open another connection -> Try to use temp table created inside 1st connection -> error. btw I'm using zeos for years and everything is working just fine.