Re: plpgsql_check_function issue after upgrade
Hi for record - the problem was probably in using plpgsql_check together with PL debugger (plugin_debugger). Both extensions use the PLpgSQL DBG API, but this API is not designed to be used by more extensions. plpgsql_check has implemented some workaround, but it should be loaded last if this workaround should work. Older versions of plpgsq_check (without profiler) didn't use DBG API. https://github.com/okbob/plpgsql_check#using-with-plugin_debugger Regards Pavel
Monitoring-only role
Hi there! Is there (a way to have) a "monitoring only" role in PG? This role should have access to stat views only,maybe something more, but no access to data whatsoever. Tnx!
Re: Monitoring-only role
Hey, There is pg_monitor role. I can "Read/execute various monitoring views and functions. This role is a member of pg_read_all_settings, pg_read_all_stats and pg_stat_scan_tables." https://www.postgresql.org/docs/14/predefined-roles.html Cheers, Mateusz wt., 6 gru 2022 o 13:37 Wiwwo Staff napisał(a): > Hi there! > Is there (a way to have) a "monitoring only" role in PG? > This role should have access to stat views only,maybe something more, but > no access to data whatsoever. > > Tnx! >
Re: plpgsql_check_function issue after upgrade
On Tue, 6 Dec, 2022, 4:17 pm Pavel Stehule, wrote: > Hi > > for record - the problem was probably in using plpgsql_check together with > PL debugger (plugin_debugger). Both extensions use the PLpgSQL DBG API, but > this API is not designed to be used by more extensions. plpgsql_check has > implemented some workaround, but it should be loaded last if this > workaround should work. Older versions of plpgsq_check (without profiler) > didn't use DBG API. > > https://github.com/okbob/plpgsql_check#using-with-plugin_debugger > I am loading the plpgsql_chect last after loading other extensions. Looks like it is working for now, but need more time to check on it. > > Regards > > Pavel >
Re: postgres large database backup
On Thu, Dec 1, 2022 at 7:40 AM Vijaykumar Jain wrote: > > >> I do not recall zfs snapshots took anything resource intensive, and it was >> quick.ill ask around for actual time. > > > Ok just a small note, out ingestion pattern is write anywhere, read globally. > So we did stop ingestion while snapshot was taken as we could afford it that > way. Maybe the story is different when snapshot is taken on live systems > which generate a lot of delta. Snapshot in ZFS at worst case would copy the entire allocation tree and adjusts ref counters, IE metadata, no data copy. I don't know if it even works that hard to create a snapshot now, as in it might just make a marker, all I know is they've always been fast/cheap. Differential zfs send|recv based off two snapshots is also pretty damn fast because it knows what's shared, and only sends what changes. There's definitely been major changes in how snapshots are created over the years to make them even quicker (ISTR it's the "bookmarks" feature?) This is just a small pool on my local/home NAS (TrueNAS Scale) of around 40T of data...Note that -r, it's not creating one snapshot but uhm *checks* 64 (-r create also a snapshot of every volume/filesystem underneath that) root@...:~ # time zfs snapshot -r tank@TESTSNAP0 0.000u 0.028s 0:00.32 6.2% 144+280k 0+0io 0pf+0w root@...:~ # I have no idea how many files are in there. My personal home directory and dev tree is in one of those, and I've got at least half a dozen versions of the Linux Kernel, FreeBSD kernel, and other source trees, and quite a few other Very Bushy(tm) source trees so it's quite a fair amount of files. So yeah, 28msec, 64 snapshotsthey're REALLY cheap to create, and since you pay the performance costs already, they're not very expensive to maintain. And the performance cost isn't awful unlike in more traditional snapshot systems. I will say that is a kind of optimal case because I have a very fast NVMe SLOG/ZIL, and the box is otherwise effectively idle. Destroying the freshly created snapshot is about the same...So is destroying 6 months old snapshots though I don't have a bonkers amount of changed data in my pool. -- "Genius might be described as a supreme capacity for getting its possessors into trouble of all kinds." -- Samuel Butler
PG 14.5 -- Impossible to restore dump due to interaction/order of views, functions, and generated columns
Good afternoon, I've recently run into a weird issue that I'm trying to gather more data on before sending an official bug report on the off chance that it's already been addressed. Within my schema there is a table that has a GENERATED ALWAYS column that calls a plpgsql function. The called function has a "row type" variable declared that references a view. While the schema itself functions properly day to day, and pg_dumpall works as expected, the generated SQL fails to successfully execute. The table in question is restored with no rows, and an error is generated during the COPY stating that the type does not exist. The issue appears to be that the COPY statement for the data is trying to execute the function specified for the GENERATED ALWAYS column, and that function cannot run because the view that the function references does not yet exist. The dump was made with: pg_dumpall -c --quote-all-identifiers --exclude-database=postgres --exclude-database=template0 --exclude-database=template1 Is this a known or unknown issue, or am I just missing something? Any insight is appreciated. Please reply-all as I'm not currently subscribed to the list. Thanks in advance!
Re: PG 14.5 -- Impossible to restore dump due to interaction/order of views, functions, and generated columns
"Nunya Business" writes: > Within my schema there is a table that has a GENERATED ALWAYS column > that calls a plpgsql function. The called function has a "row type" > variable declared that references a view. While the schema itself > functions properly day to day, and pg_dumpall works as expected, the > generated SQL fails to successfully execute. The table in question is > restored with no rows, and an error is generated during the COPY stating > that the type does not exist. Hmm, do you have actually circular dependencies in that? pg_dump has some heuristics for dealing with such cases, but maybe it needs more. Please create a self-contained example and submit it to pgsql-bugs. regards, tom lane
Re: PG 14.5 -- Impossible to restore dump due to interaction/order of views, functions, and generated columns
On 12/5/22 11:49, Nunya Business wrote: Good afternoon, I've recently run into a weird issue that I'm trying to gather more data on before sending an official bug report on the off chance that it's already been addressed. Within my schema there is a table that has a GENERATED ALWAYS column that calls a plpgsql function. The called function has a "row type" variable declared that references a view. While the schema itself functions properly day to day, and pg_dumpall works as expected, the generated SQL fails to successfully execute. The table in question is restored with no rows, and an error is generated during the COPY stating that the type does not exist. The issue appears to be that the COPY statement for the data is trying to execute the function specified for the GENERATED ALWAYS column, and that function cannot run because the view that the function references does not yet exist. The dump was made with: pg_dumpall -c --quote-all-identifiers --exclude-database=postgres --exclude-database=template0 --exclude-database=template1 Is this a known or unknown issue, or am I just missing something? https://www.postgresql.org/docs/current/sql-createtable.html "GENERATED ALWAYS AS ( generation_expr ) STORED This clause creates the column as a generated column. The column cannot be written to, and when read the result of the specified expression will be returned. The keyword STORED is required to signify that the column will be computed on write and will be stored on disk. The generation expression can refer to other columns in the table, but not other generated columns. Any functions and operators used must be immutable. **References to other tables are not allowed.** " Emphasis(**) added. I'm going to say hiding the table/view reference in a function is not going to work any better then when folks try that in a CHECK constraint. Any insight is appreciated. Please reply-all as I'm not currently subscribed to the list. Thanks in advance! -- Adrian Klaver adrian.kla...@aklaver.com
Suboptimal GIST index?
Greetings! I am getting the following message using DEBUG logging, telling me that a particular GIST index is suboptimal. This is for a table with ids and date ranges that are supposed to be non-overlapping. Here is the index def: "my_fact_table_id_as_of_date_excl" EXCLUDE USING gist (id WITH =, as_of_date WITH &&) This is on PG 13.9, but I got this exact same message on the same cluster when it was on version 10. The fact table has about 250m rows and is about 275GB in size. Here is the approximate log message: 2022-12-06 06:00:00.152 GMT,"me","foo",3559072,"0.0.0.0:5000",638ead26.364ea0,27080,"SELECT",2022-12-06 02:47:02 GMT,30/296653,2464130682,DEBUG,XX000,"picksplit method for column 2 of index ""my_fact_table_id_as_of_date_excl"" failed",,"The index is not optimal. To optimize it, contact a developer, or try to use the column as the second one in the CREATE INDEX command.",,,"SQL statement ""WITH ended_last_fact AS (UPDATE my_fact_table SET as_of_date = daterange(lower(as_of_date), v_as_of_date_start) , updated = v_updated WHERE id = v_id AND lower(as_of_date) <> v_as_of_date_start AND upper(as_of_date) = 'infinity' RETURNING *) INSERT INTO my_fact_table AS f ( as_of_date , customer_id , foo , id updated) SELECT daterange(v_as_of_date_start, 'infinity') , v_customer_id , 'mock' AS foo , v_id , v_updated FROM (SELECT v_id AS id) nes LEFT JOIN ended_last_fact ON nes.id = ended_last_fact.id ON CONFLICT (id, lower(daterange(as_of_date))) DO UPDATE SET foo = f.foo , updated = f.updated; So I suppose this means I could be getting better performance but in any case, I don't understand the hint suggested. Any insight is much appreciated. Thanks! Jeremy
snapshot question
Per the following link: https://www.sobyte.net/post/2022-08/postgres-snapshot/ To get a snapshot we need to know xmin, xmax and xip. my question is why do we need them? Since every tuple in a page has t_xmin and t_xmax based on the page layout( https://www.postgresql.org/docs/current/storage-page-layout.html), if we has a snapshot at xip 12345, couldn't we just check every tuple whose t_xmin is lower than 12345 and already committed? Thanks, Qihua
Re: snapshot question
On Wed, Dec 7, 2022 at 10:20:09AM +0800, qihua wu wrote: > Per the following link: https://www.sobyte.net/post/2022-08/postgres-snapshot/ > To get a snapshot we need to know xmin, xmax and xip. my question is why do we > need them? Since every tuple in a page has t_xmin and t_xmax based on the page > layout(https://www.postgresql.org/docs/current/storage-page-layout.html), if > we > has a snapshot at xip 12345, couldn't we just check every tuple whose t_xmin > is > lower than 12345 and already committed? See, slides 9-11: https://momjian.us/main/presentations/internals.html#mvcc xip is a list of transaction that were open at the time the snapshot was taken. If the transaction commits after the snapshot is taken, it is still invisible. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Embrace your flaws. They make you human, rather than perfect, which you will never be.
