Re: Getting PSQL in Windows to support TAB/Autocomplete via modified readline...
Hi, On 2022-11-28 17:22:19 -0500, Kirk Wolak wrote: > Thank you for this. We have the build working with meson (w/o readline). > I was not able to get "readline" to be recognized by meson (I've attached > the log file) > It is "there" according to vcpkg. (other projects have a .pc file this one > is missing?) The problem is likely indicated by this: > env[PKG_CONFIG_PATH]: c:/vcpkg/installed/x64-windows/tools/pkgconf/pkgconf.exe This needs to point to the directory containing the .pc files, not the executable. Greetings, Andres Freund
RE: About row locking ordering
Thank you for your comments. Sorry for the late reply. From: Kirk Wolak Sent: Friday, November 25, 2022 6:12 PM > My first question is why are you not using "WHERE CURRENT OF" cursor_name? I thought that cursors are preferred for manipulating large numbers of rows. So I did not consider using cursors in this test case because each process manipulates only one row. However, assuming general usage, I thought it would be preferable to use a cursor, as you pointed out, because it might result in fewer table accesses. I didn't have that knowledge. > But effectively, you are locking the row and that is the row you want to > update (the current row of the cursor). > I wonder if that addresses the problem... I tested it with a cursor, but there was still a problem.. I will attach the script used for the test, though it will be simple. <>
Re[2]: 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 Thanks Tom. There are indeed circular references in the schema and the whole thing sort of doesn't pass the smell test, but this is my first look at it. The generated column on the table calls a function which selects from a view that references the table. The production schema where I ran into this is pretty large and complex, so the contrived example that follows may not be the minimum working example but it's pretty small and has the same behavior regarding the SQL generated by pg_dumpall. It seems that the schema is probably invalid according to the GENERATED rules and that pg_dumpall is operating as intended, but somehow the check in the ALTER TABLE isn't deep enough to prevent the issue, but maybe I'm mistaken. Once this is created, if you insert a few rows and execute pg_dumpall, the resulting SQL cannot be loaded and will fail during the COPY, complaining that the view referenced by the function doesn't exist. Here is the schema. CCing pgsql-bugs as requested. --CUT CREATE TABLE tblA ( id serial unique not null, dt timestamp with time zone not null default now(), data text ); CREATE OR REPLACE VIEW viewA as ( SELECT sum(id) FROM tblA ); CREATE OR REPLACE FUNCTION fnA(askid INTEGER) RETURNS integer LANGUAGE 'plpgsql' IMMUTABLE AS $$ declare varA viewA; ret integer; begin SELECT viewA.* INTO varA FROM viewA; ret = varA.sum; return ret; end; $$; ALTER TABLE tblA ADD COLUMN idsum INTEGER GENERATED ALWAYS AS (fnA(id)) STORED;
Re: Re[2]: PG 14.5 -- Impossible to restore dump due to interaction/order of views, functions, and generated columns
"Nunya Business" writes: > Thanks Tom. There are indeed circular references in the schema and the > whole thing sort of doesn't pass the smell test, but this is my first > look at it. The generated column on the table calls a function which > selects from a view that references the table. The production schema > where I ran into this is pretty large and complex, so the contrived > example that follows may not be the minimum working example but it's > pretty small and has the same behavior regarding the SQL generated by > pg_dumpall. Hm. The actual problem here is that fnA() is making unwarranted assumptions about the search_path it's run under, so it fails when the pg_dump script invokes it with a restrictive search_path. If you change the function text so that the references to viewA are schema-qualified, then it restores without errors. "Doesn't pass the smell test" is putting it mildly, btw. Labeling that function IMMUTABLE is a huge lie, and what it means is that your GENERATED column doesn't have the amount of stability that it's supposed to per spec. I'm not sure exactly what sorts of misbehaviors might ensue from that, but I'm pretty certain that the data in the GENERATED column after dump/restore won't match what you had there beforehand. regards, tom lane
Re[4]: PG 14.5 -- Impossible to restore dump due to interaction/order of views, functions, and generated columns
Tom, Thanks for the insight, I didn't even consider the search path being an issue and I should have. I saw it explicitly specified in other parts of the dump and just assumed it was being done in the function as well. For example, the CREATE statements in the dump output all specify the schema name even though it's not specified in the original statements. I suppose expecting statements in the function body to be similarly qualified in the dump is unreasonable considering the complexity of doing so. Anyway, thanks for the tip. If I run into something like this again in the future I'll have a better idea of where to look and how to recover what data I can in an emergency. -- Original Message -- From "Tom Lane" To "Nunya Business" Cc pgsql-gene...@postgresql.org; pgsql-b...@postgresql.org Date 2022-12-06 15:47:26 Subject Re: Re[2]: PG 14.5 -- Impossible to restore dump due to interaction/order of views, functions, and generated columns "Nunya Business" writes: Thanks Tom. There are indeed circular references in the schema and the whole thing sort of doesn't pass the smell test, but this is my first look at it. The generated column on the table calls a function which selects from a view that references the table. The production schema where I ran into this is pretty large and complex, so the contrived example that follows may not be the minimum working example but it's pretty small and has the same behavior regarding the SQL generated by pg_dumpall. Hm. The actual problem here is that fnA() is making unwarranted assumptions about the search_path it's run under, so it fails when the pg_dump script invokes it with a restrictive search_path. If you change the function text so that the references to viewA are schema-qualified, then it restores without errors. "Doesn't pass the smell test" is putting it mildly, btw. Labeling that function IMMUTABLE is a huge lie, and what it means is that your GENERATED column doesn't have the amount of stability that it's supposed to per spec. I'm not sure exactly what sorts of misbehaviors might ensue from that, but I'm pretty certain that the data in the GENERATED column after dump/restore won't match what you had there beforehand. regards, tom lane
Is there a way to know write statistics on an individual index
Hi, I know there's a pg_stats_user_indexes that gives statistics about the index scans/fetches, but I don't find any information about how many writes(insert/update/delete) happen on this individual index. Is there some tool that could achieve this? Thanks, Alun
Reading from replication slot fails if publication is dropped and created.
Hi, Have a scenario, where the publication was accidentely deleted and eventhough the publication was re-created, trying to read data from the replication slot fails with the following error message. "ERROR: publication \"dbz_publication\" does not exist\n Where: slot \"s20d07aebf4204109872de556b66b0afb\", output plugin \"pgoutput\", in the change callback, associated LSN 3D/717714F0" Aware that the publication would have to be created first and then the replication slow should follow. Is there any way to have the system working without having to delete the replication slot and re-create all over? i.e create publication and set the appropriate references ? Note; Restarted the db instance after re-creating the publication (presume some internal model assocating the publication -> tables -> replication slot is broken). Appreciate thought/suggestions. Thanks.
