Re: COMMIT IN STORED PROCEDURE WHILE IN A LOOP
> On Oct 18, 2022, at 19:18, gogala.mla...@gmail.com wrote: > > Commit within a loop is an extremely bad idea. This is an over-generalization. There are many use-cases for this (if there were not, procedures wouldn't have been nearly as important a feature). For example, if you are processing a large update (in the hundreds of thousands or more of rows), you often want to commit regularly so that other processes don't have to wait for the whole thing to finish due to row-level locks, and to give vacuum a chance to deal with the dead tuples. Similarly, while inserting one row at a time and committing is usually not a great idea, it can make sense to do large inserts in batches. Applications do this kind of thing all the time, very successfully; it was just that the loop was in the application rather than in the procedure. High commit rates happen all the time, and they don't break PostgreSQL. For example, an IoT application collecting sensor data and doing many inserts per second is also doing many commits per second, since each bare INSERT is in its own transaction. PostgreSQL handles it just fine.
Re: Attaching database
Hi, On Tue, Oct 18, 2022 at 10:06:40PM -0500, Igor Korot wrote: > Hi, guys, > After reading the documentation on > https://www.postgresql.org/docs/current/postgres-fdw.html > and checking the example I have a different question. > > The presentation in the link referenced doesn't explain how to get the > table list on the > remote server and the information on the specific table. > > The example tals about connection and creating a linked table, but it > doesn't explain > how to know the information about the tables on the remote > > Is it possible or I will have to know this beforehand? In general it's up to you to make sure that the remote table definition matches the local one. You can use IMPORT FOREIGN SCHEMA (1) to automate this process, but you will still have to make sure that any subsequent modification on the remote table (added/removed column, changed datatype...) is also done on the local foreign table. > (Sorry for the top-posting). This isn't really related to the previous discussion so it's quite ok, although it's usually a good practice to trim the unwanted parts of the previous message (in this case the whole previous message). [1] https://www.postgresql.org/docs/current/sql-importforeignschema.html
Re: Attaching database
Hi, guys, After reading the documentation on https://www.postgresql.org/docs/current/postgres-fdw.html and checking the example I have a different question. The presentation in the link referenced doesn't explain how to get the table list on the remote server and the information on the specific table. The example tals about connection and creating a linked table, but it doesn't explain how to know the information about the tables on the remote Is it possible or I will have to know this beforehand? (Sorry for the top-posting). Thank you. On Sat, Oct 15, 2022 at 5:57 PM Alex Theodossis wrote: > > Hi, > > you can only run inquires now on information_schema for the database you > are connected to. > > If you are looking/searching in pg_database though, you can information > globally. > > Something changed recently (I am using Dbeaver); I was able to attach to > a single database and see all my databases; run queries, etc. > > With the latest update, now you have to have a separate connection to > each database. > > Navicat now does the same thing, so my thinking is they must have > changed the drivers. > > Regards, > > On 10/15/22 02:06, Julien Rouhaud wrote: > > Hi, > > > > On Fri, Oct 14, 2022 at 11:16:44PM -0500, Igor Korot wrote: > >> Sorry for resurrecting this old thread... > >> If an attaching the DB creates new connection which will be cmpletely > >> independent - how the INFORMATION_SCHEMA.table@table_catalog > >> field is handled. > >> > >> Lets say I open connection to the DB (finance) and then attached another DB > >> (finance_2021). > >> > >> So, when I call SELECT table_schema, table_name FROM > >> INFORMATION_SCHEMA.table > >> I will get all tables from (finance) DB only. > >> And to get all tables from (finance_2021) I need to make this catalog > >> current > >> and reissue the query. > >> > >> Am I right? > > No. In postgres, databases are completely disjoint containers and once you > > have a connection on a given database it will stay on that database, > > there's no > > way around that. > > > > Using postgres_fdw allows you to create a local table that will point to > > another table, possibly on another database or even another server, but it > > will > > still be a (foreign) table, that has to be created in the current database > > in > > the schema of your choice. > > > > Depending on your use case, maybe what you could do is create a finance_2021 > > schema, and create all the foreign tables you need in that schema pointing > > to > > the finance_2021 database. Any table existing in both "finance" and > > "finance_2021" will then be visible in information_schema.tables, with a > > different table_schema. If you have multiple schemas in each database, then > > find a way to make it work, maybe adding a _2021 suffix on each schema or > > something like that. > > > > You can then maybe use the search_path (see > > https://www.postgresql.org/docs/current/ddl-schemas.html#DDL-SCHEMAS-PATH) > > to > > use by default one of the set of tables rather than the other. But in any > > case, it's only a workaround that has to be implemented on your client, as > > you > > will always be connected on the same database, and see both set of object in > > information_schema. > > > > > -- > Alex Theodossis > a...@dossi.info > 347-514-5420 > > >
Re: COMMIT IN STORED PROCEDURE WHILE IN A LOOP
On Tue, 2022-10-18 at 14:31 -0700, Christophe Pettus wrote: > > Rather than have a loop inside the BEGIN / END, you could put the > BEGIN EXCEPTION END inside the loop, catch the error, store the > important parts of the exception in a variable, and then do the > COMMIT after the END statement but before the next iteration of the > loop. A bit messier, but it gets the job done. Commit within a loop is an extremely bad idea. Commit is an expensive operation which includes incrementing the XID, which is global and not local to the process. There is also a WAL write which has to be waited on. Every commit implies at least one write operation. If that was not the case, Postgres wouldn't be ACID compliant. There would be problem with the "D". Commit within a loop will have many adverse effects on the performance. Here is what Tom Kyte, who used to be a celebrity in the world of Oracle, said on this topic: https://asktom.oracle.com/pls/apex/f?p=100:11:0p11_question_id:4951966319022 Surprisingly enough, the argument in the article is mostly portable, doesn't depend on the database type at all. Basically, transaction is a logical unit of work. If 1000 rows need to be updated, it's better to update them in a single transaction than in 1000 transactions. That is particularly true for Postgres which doesn't have problems with the undo tablespace and ORA-1555 "snapshot too old" error because of the different architecture. Also, Oracle has a secret optimization: it doesn't wait for commit, if the commit is issued within PL/SQL loop. Your idea solves the syntactic problem with commits within PLPG/SQL loops but it doesn't solve other problems that such programming causes. Commit within loop is a very bad idea. The best way to resolve the problems with commit within the loop is to remove the programmer trying to do that from the project. In my humble opinion, programmers who do stuff like that should suffer unusual and cruel punishment. PS: I am sure, that if Tom Kyte would dare to make a suggestion on this list, there would be someone who would try to explain "the Postgres way" to him in a condescending manner. I've seen that before.
Re: COMMIT IN STORED PROCEDURE WHILE IN A LOOP
On Tue, 2022-10-18 at 17:33 -0400, Tom Lane wrote: > Bryn Llewellyn writes: > > x...@thebuild.com wrote: > > > You can commit in a loop, but not in BEGIN / END block that has > > > an exception handler: that creates a subtransaction for the > > > duration of the BEGIN / END. > > > This surprised me when I first started to use PG (after all those > > years > > with ORCL). > > Really? BEGIN with an exception block is a subtransaction because > it's > defined to roll back to the database state as of the start of the > block > if an exception occurs. COMMIT in the middle fundamentally conflicts > with that, I should think. Does Oracle interpret that differently? > > regards, tom lane > > Hi Tom, Yes, Oracle does interpret that differently. Bryn may correct me if I'm wrong, but Oracle creates an implicit save point when it encounters BEGIN. Exception handler doesn't necessarily roll things back. Oracle behavior is not standard and PgSQL adheres to SQL standard better than Oracle. However, being as pervasive as it is, Oracle is de facto standard. Also, Oracle has something called "autonomous transaction" which, in effect, means that a session can have two concurrent transactions open, which is also non-standard: https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/AUTONOMOUS_TRANSACTION-pragma.html#GUID-AD33D949-081B-4CD3-A240-C29773E908C3 Amazon, lead by Kevin Closson, the guy who has famously designed Oracle Exadata among other things, even came up with the recipe how to migrate it to Postgres: https://aws.amazon.com/blogs/database/migrating-oracle-autonomous-transactions-to-postgresql/ I am a bit skeptical toward that recipe and I usually prefer programming solutions with opening another thread and sending a message. BTW, speaking of Kevin, he has also written pgio, which is a PostgreSQL version of his SLOB package. Kevin is the only retired Oracle ACE in existence. BTW, Bryn also used to be an Oracle ACE. Regards
Re: what's inherited
On Tue, 2022-10-18 at 16:41 -0500, Ted Toth wrote: > When I create a table that inherits from another table what all is > inherited i.e. ownership, security policy, ...? None of that, only the column definitione. Yours, Laurenz Albe
Re: COMMIT IN STORED PROCEDURE WHILE IN A LOOP
On Tue, 2022-10-18 at 17:33 -0400, Tom Lane wrote: > Bryn Llewellyn writes: > > x...@thebuild.com wrote: > > > You can commit in a loop, but not in BEGIN / END block that has an > > > exception handler: that creates a subtransaction for the duration of the > > > BEGIN / END. > > > This surprised me when I first started to use PG (after all those years > > with ORCL). > > Really? BEGIN with an exception block is a subtransaction because it's > defined to roll back to the database state as of the start of the block > if an exception occurs. COMMIT in the middle fundamentally conflicts > with that, I should think. Does Oracle interpret that differently? Looks like Oracle doesn't care much about that: SQL> CREATE TABLE mytab (id integer CHECK (id > 0)); Table created. SQL> CREATE PROCEDURE committest IS 2 BEGIN 3 INSERT INTO mytab VALUES (42); 4 COMMIT; 5 INSERT INTO mytab VALUES (-42); 6 EXCEPTION 7 WHEN OTHERS THEN 8NULL; 9 END; 10 / Procedure created. SQL> CALL committest(); Call completed. SQL> SELECT * FROM mytab; ID -- 42 I looks like Oracle allows you to randomly interfere with its transaction handling. If you run commit and then enter an exception handler, it simply doesn't rollback. Yours, Laurenz Albe
what's inherited
When I create a table that inherits from another table what all is inherited i.e. ownership, security policy, ...? Ted
Re: COMMIT IN STORED PROCEDURE WHILE IN A LOOP
> On Oct 18, 2022, at 14:15, Bryn Llewellyn wrote: > Could the limitation be lifted by making tractable internal implementation > changes? Or is it rooted in profoundly deep features of the > architecture—meaning that it could never be lifted? That is a very good question. One of the issues (as I understand it) is to be able to continue to use the same connection in the event of an error. Here's the scenario: 1. SELECT * FROM my_func(); 2. my_func gets an error. The connection is now in an error state. 3. So, catching the exception doesn't do you much good, because future operations will get an error. This problem is solved by wrapping it in a savepoint, since rolling back to the savepoint will undo the error state of the connection. There might be a way forward, but it's hard to get the proper semantics of an exception handler without doing something like that.
Re: COMMIT IN STORED PROCEDURE WHILE IN A LOOP
Bryn Llewellyn writes: > x...@thebuild.com wrote: >> You can commit in a loop, but not in BEGIN / END block that has an exception >> handler: that creates a subtransaction for the duration of the BEGIN / END. > This surprised me when I first started to use PG (after all those years > with ORCL). Really? BEGIN with an exception block is a subtransaction because it's defined to roll back to the database state as of the start of the block if an exception occurs. COMMIT in the middle fundamentally conflicts with that, I should think. Does Oracle interpret that differently? regards, tom lane
Re: COMMIT IN STORED PROCEDURE WHILE IN A LOOP
> On Oct 18, 2022, at 14:29, Ravi Krishna wrote: > > > You can commit in a loop, but not in BEGIN / END block that has an > > exception handler: > > that creates a subtransaction for the duration of the BEGIN / END. > > The reason I have to deal with error exception is that I want to ignore > failure on a table and move on to next table. Rather than have a loop inside the BEGIN / END, you could put the BEGIN EXCEPTION END inside the loop, catch the error, store the important parts of the exception in a variable, and then do the COMMIT after the END statement but before the next iteration of the loop. A bit messier, but it gets the job done.
Re: COMMIT IN STORED PROCEDURE WHILE IN A LOOP
> You can commit in a loop, but not in BEGIN / END block that has an exception > handler:> that creates a subtransaction for the duration of the BEGIN / END. The reason I have to deal with error exception is that I want to ignore failure on a table and move on to next table. I thought I can trick it by moving the part of the code which executes dynamic sql and has exception handler in it,to a separate procedure. This way I can keep the main procedure without exception handler. It failed too withthe same error. So that means this restriction is not at lexical level, but at execution level.This is so disappointing. It seems there is no way I can copy each table and commit it individually and also capture error.
Re: COMMIT IN STORED PROCEDURE WHILE IN A LOOP
> x...@thebuild.com wrote: > >> s_ravikris...@aol.com wrote: >> >> I am getting error at COMMIT -> cannot commit while a subtransaction is >> active... > > You can commit in a loop, but not in BEGIN / END block that has an exception > handler: that creates a subtransaction for the duration of the BEGIN / END. This surprised me when I first started to use PG (after all those years with ORCL). There are some use cases where this limitation is a nuisance. For example, if you want to implement the famous retry loop (with exponential backoff) for an error that is provoked at commit time... well, you have to do it in client-side code. Could the limitation be lifted by making tractable internal implementation changes? Or is it rooted in profoundly deep features of the architecture—meaning that it could never be lifted?
Re: COMMIT IN STORED PROCEDURE WHILE IN A LOOP
> On Oct 18, 2022, at 13:14, Ravi Krishna wrote: > > I am getting error at COMMIT -> cannot commit while a subtransaction is > active. > Is commit not possible in a loop You can commit in a loop, but not in BEGIN / END block that has an exception handler: that creates a subtransaction for the duration of the BEGIN / END.
COMMIT IN STORED PROCEDURE WHILE IN A LOOP
AWS Aurora based on PG 13 I am writing a sproc to copy a schema into another. Here is the relevant portion of the code. Basically I want to commit after every table is created. In big schemas with hundreds of table I do not want to run entire operation in one transaction. I am getting error at COMMIT -> cannot commit while a subtransaction is active. Is commit not possible in a loop BEGIN FOR object IN SELECT table_name::text FROM information_schema.TABLES WHERE table_schema = source_schema AND table_type = 'BASE TABLE' LOOP buffer := dest_schema || '.' || object; BEGIN sql_stmt := 'CREATE TABLE ' || buffer || ' (LIKE ' || source_schema || '.' || object; sql_stmt := sql_stmt || ' INCLUDING COMMENTS INCLUDING IDENTITY INCLUDING CONSTRAINTS INCLUDING DEFAULTS INCLUDING GENERATED INCLUDING INDEXES)' ; --RAISE NOTICE '%' , sql_stmt ; execute sql_stmt ; COMMIT; sql_stmt := 'INSERT INTO ' || buffer || ' OVERRIDING SYSTEM VALUE SELECT * FROM ' || source_schema || '.' || object ; --RAISE NOTICE '%' , sql_stmt ; execute sql_stmt ; COMMIT; EXCEPTION WHEN OTHERS THEN GET STACKED DIAGNOSTICS text_var1 = MESSAGE_TEXT, text_var2 = PG_EXCEPTION_DETAIL, text_var3 = PG_EXCEPTION_HINT; print_msg := 'ERROR:->' || text_var1 || '|' || text_var2 || '|' || text_var3 ; RAISE NOTICE '%' , print_msg ; END ; END LOOP; END;
Re: byte-size of column values
Dominique Devienne writes: > Hi. I'm surprised by the result for bit(3) and char, when calling > pg_column_size(). > Why 6, instead of 1? The doc does mention 5-8 bytes overhead, but I > expected those for varying bit, not fixed-sized bit typed values. How > come? Your expectation is incorrect. Postgres always treats these types as variable-length, whether or not the column has a length constraint. Thus, there's always a header to store the actual length. That can be either 1 or 4 bytes (I think the doc you are looking at might be a little out of date on that point). Because of the popularity of variable-width character encodings, a column declared as N characters wide isn't necessarily a fixed number of bytes wide, making it a lot less useful than you might think to have optimizations for fixed-width storage. Between that and the fact that most Postgres developers regard CHAR(N) as an obsolete hangover from the days of punched cards, no such optimizations have been attempted. regards, tom lane
Re: byte-size of column values
On Tue, Oct 18, 2022 at 8:53 AM Dominique Devienne wrote: > Hi. I'm surprised by the result for bit(3) and char, when calling > pg_column_size(). > > Why 6, instead of 1? The doc does mention 5-8 bytes overhead, but I > expected those for varying bit, not fixed-sized bit typed values. How > come? > > The base type is what matters, if the length of the actual type is a parameter (the (n) part) the underlying type must be variable, and thus has a component that says how long the actually stored value is. > How does one store as compactedly as possible several small enums, on > millions of rows? > int2 David J. p.s., pretend char doesn't even exist.
byte-size of column values
Hi. I'm surprised by the result for bit(3) and char, when calling pg_column_size(). Why 6, instead of 1? The doc does mention 5-8 bytes overhead, but I expected those for varying bit, not fixed-sized bit typed values. How come? Similarly, why 2 for char? Is it linked to Unicode? 1 byte for the varying UTF-8 encoded length, then the (potentially) multi-byte encoding? (the very bit pattern of UTF-8 allows to infer the encoded length, so storing the length explicitly is theoretically not even necessary) Similarly, enums are always 4 bytes I read, despite rarely exceeding cardinalities beyond a single byte can store. How does one store as compactedly as possible several small enums, on millions of rows? And is the minimum column size always 2? I'm sure many we call out "premature optimization", but isn't using 32 bits instead of 2, 3 (or 8, to round to a byte) wasteful, in disk space, thus then ultimately energy? (OK, that last one is pushing it :) ). I'm sure there are reasons for the above. And I guess I'm curious about them. Thanks, --DD ddevienne=> create table foo (b3 bit(3), i2 int2, i4 int4, i8 int8, c char); CREATE TABLE ddevienne=> insert into foo values (b'101', 1002, 1004, 1008, 'C'); INSERT 0 1 ddevienne=> select pg_column_size(b3), pg_column_size(i2), pg_column_size(i4), pg_column_size(i8), pg_column_size(c) from foo; pg_column_size | pg_column_size | pg_column_size | pg_column_size | pg_column_size ++++ 6 | 2 | 4 | 8 | 2 (1 row)
