Re: Differences in Escaped bytea's when creating a plain pg_dump
Am 27.06.2022 um 12:12 schrieb Daniel Verite: WR wrote: First run worked. Second run worked. Then I changed to SET standard_conforming_strings = off; Third run worked. Fourth run throw the error Then I changed back to SET standard_conforming_strings = on; Fifth run throw the error too. And only adding E and second backslash helped. This kind of unstable behavior can be seen if the SET may not be executed by the same session (same connection to the server) as the subsequent queries. SET affects only the session it's being run in. For instance a connection pooler configured in statement mode may produce that behavior. The solution in the case of a connection pooler is to group related statements into a transaction. Maybe pgAdmin has a pooler like that, but if you're issuing the statements in the same SQL window, I would find it quite surprising that it doesn't run them by the same session. Or maybe you're mixing queries from different SQL windows that each have their own connection, and in some cases you do the SET in a window and the INSERT in another window. Or maybe it's a pgAdmin bug. Ok, thank you Daniel, in all tests I put the SET statement before the INSERT in the same query tool and run it as a whole. (no statement was marked by mouse for single execution). So I agree with you, that one transmission is used by pgAdmin to run SET and INSERT. I always had a SET before each INSERT, so there could be no unknown state of standard_conforming_strings, no matter if pdAdmin uses different connections for each run of the script or the same again. The supplement of the second backslash in the c++ code costs me some nerves. Sending an statement std::string without doublebackslashes to pqxx::transaction results in the "0x00 is not a valid UTF8 sequence" error 21020 (as I wrote already). I'm afraid, I can't simply replace all backslashes in the whole dumptext with two of them. Since I found out now, that psql works nice with my dumpfiles, maybe its easier, to call psql from my code and not to use a pqxx::transaction. So I can also save myself loading the dump from the sql-file. The more I think about it, the more I love this idea. Thanks a lot, Wolfgang -- Wolfgang Rißler mailto: wolfgang.riss...@freenet.de mobil: +49 1520 9191759 - may the source be with you -
Re: Differences in Escaped bytea's when creating a plain pg_dump
Am 27.06.2022 um 09:32 schrieb David G. Johnston: [snip] I suggest doing self-contained examples that demonstrate the documented behavior not working as documented (or not being functional even if intended) to pinpoint any bug that might be lurking here. With only fragments and statements that seem impossible we are left to assume operator error. pg_dump is completely correct in what it is producing (non-escape literal \000). I also suggest using psql and pg_dump directly, and not pgAdmin, to demonstrate a core PostgreSQL bug. David J. Thank you David, I followed you advice, using pg_dump and psql directly. And the in in contrast to pgAdmin psql works like expected and reproducable again and again. With SET standard_conforming_strings = on; an INSERT without E and double backslash works. SET standard_conforming_strings = off; I get the warning and the error. So there is no core PostgreSQL bug, I think. PgAdmin has different result, when running the same sql commands repeatedly. Before filing a bug there, I should update to the actual release. Now I will test our c++ code and will hopefully find out, why I can't run the dump from a sql-file (where is SET standard_conforming_strings = on;) as a pqxx-transaction... -- Wolfgang Rißler mailto: wolfgang.riss...@freenet.de mobil: +49 1520 9191759 - may the source be with you -
Re: DROP OWNED BY fails with #53200: ERROR: out of shared memory
Dominique Devienne: I wish for DB-specific ROLEs BTW... Same here. That would be so useful.
Compiling a ossp-uuid module for Win32
Hello community, since we need a libpq for postgres v13 for a win32 project, and I did not find a binary package, I tried to compile my own postgres v13 win32 with MSVisualStudio 2019. This works so far, as described in the docs. I also could add the most extensions in the config.pl (without knowing exactly, if they are necessary for our libpq). Only one module drives me nuts, the ossp-uuid. I cant find a binary package (win32), but I could get the sources from here: http://www.ossp.org/pkg/lib/uuid/ I can compile them in MinGW-environment (with ./configure and make), but (as expected) I get an uuid.h but an .a libraryfile like for *ix systems. The build of the libpq in postgres tells me, it needs a uuid.lib (which is also expected since it's a MS-linker). So my first question is: Do I need my libpq with ossp-uuid (we don't want to use the whole database, only the libpq) And the second question would be (if the first answer is Yes): How can I manage to get ossp-uuid compiled in that way, that I will get a static .lib (or a dynamic .lib and .dll ) (Or is there a ready to use win32 binary package somewhere to download?) The database, we're using is the v13 64bit binary-package from EDB, where ossp-uuid is needed, because we use logical replication. But I hope there is no ossp-uuid needed in the libpq. Thank you. -- - may the source be with you -
Re: Access a newer Version of PGDB (v13) with an older libpq (v10 x86)
Am 01.05.2021 um 18:26 schrieb Adrian Klaver: On 5/1/21 3:59 AM, Wolfgang Rißler wrote: This is my problem, I completely dont know, how to start compiling my own actual 32bit libpq on windows (and I would like to do it with VS 2019). For libpqxx there have been some hints how to do so in the past, and now there is a complete project, which deals with compiling it on windows with VS and CMake. But I didnt find such hints for libpq or the whole postgresDB. Have you looked at below?: https://www.postgresql.org/docs/current/install-windows.html Stupid me, I've over overlooked this. So I will collect all the tools together and give it a try. Thank you. -- - may the source be with you -
Re: Access a newer Version of PGDB (v13) with an older libpq (v10 x86)
Am 30.04.2021 um 16:16 schrieb Tom Lane: =?UTF-8?Q?Wolfgang_Ri=c3=9fler?= writes: The problem is, that our application (IDE MS-VisualStudio, C++) has to be 32bit, because of some old 32bit-dll's, which we cant kick out at the moment. So I compiled a libpqxx with the last 32bit libpq (which is v10). Uh ... what's this about "last 32-bit libpq"? Ok, I meant, the last ready to use packaged 32-bit libpq from EDB (~_0). I can believe that a particular packager (EDB, say) might not be shipping prebuilt 32-bit binaries anymore. But if you are in a position to compile your own libraries then you can certainly build any release you want as 32-bit. This is my problem, I completely dont know, how to start compiling my own actual 32bit libpq on windows (and I would like to do it with VS 2019). For libpqxx there have been some hints how to do so in the past, and now there is a complete project, which deals with compiling it on windows with VS and CMake. But I didnt find such hints for libpq or the whole postgresDB. Or is there another provider, who supplys V13 32bit binary installers for Windows? I would recommend trying to use a reasonably late-vintage libpq; we do fix bugs in it on a regular basis. The common stumbling block for cross-version situations is that the client makes assumptions about system catalog contents that are not valid in some other server release. libpq proper doesn't really touch the catalogs, so it's mostly impervious to that problem; but you'll need to test your applications. Of course we'll do. One thing is, that we load and write bytea's. And as I read, there have been some changes. All other Operations are less problematic. Thank you -- - may the source be with you -
Re: Access a newer Version of PGDB (v13) with an older libpq (v10 x86)
Am 30.04.2021 um 13:41 schrieb Laurenz Albe: > Please reply to the list> Sorry my mistake, I fixed this. > On Fri, 2021-04-30 at 13:20 +0200, Wolfgang Rißler wrote: >> Thanks for answering, but >> I didn't ask for a PostgreSQL client. I asked for the c-language-lib >> libpq. (which is maybe used by some clients). > > That *is* a client (in my book). Ok, I agree. Sorry for admonition. > >> I would use the latest libpq, but I cant, because our c++-project stucks >> in 32bit architecture. The latest 32bit pqlib is that from v10. > > You can build libpq on a 32-bit system. No problem. Yes it is: I'm missing the experience. Until now I took the libpq from the actual WindowsInstaller-installation from EDB. But they dont provide 32bit anymore. PG10 ist the last one. Compiling a libpqxx on this base with VisualStudio and CMake works like a charm. Can you give a hint, how to build a pqlib v13 x86 in windows with VS? > >> Can I access a PostgreSQL v13 Database with this pqlib safely? > > Yes. This makes me happy. Thank you. [snip] -- - may the source be with you -
Re: Access a newer Version of PGDB (v13) with an older libpq (v10 x86)
Am 30.04.2021 um 13:00 schrieb Laurenz Albe: On Fri, 2021-04-30 at 10:49 +0200, Wolfgang Rißler wrote: we are (unfortunately) on windows with our DB and our application. Now we want to switch to a new version of Postgres, because we want to use logical replication (to replace our old Postgres 8.3 with slony-replication). The problem is, that our application (IDE MS-VisualStudio, C++) has to be 32bit, because of some old 32bit-dll's, which we cant kick out at the moment. So I compiled a libpqxx with the last 32bit libpq (which is v10). I tried to access a PostgresDB 64bit v10, and it worked very nice. So my question is, could (or should) we rather use PostgresDB v13 (to get the newest) or should we stay on PostgresDB v10 (to be more compatible to our libpq/libpqxx). Use the latest PostgreSQL client if you can, it shouldn't be a problem. Yours, Laurenz Albe Thanks for answering, but I didn't ask for a PostgreSQL client. I asked for the c-language-lib libpq. (which is maybe used by some clients). I would use the latest libpq, but I cant, because our c++-project stucks in 32bit architecture. The latest 32bit pqlib is that from v10. Can I access a PostgreSQL v13 Database with this pqlib safely? -- Wolfgang Rißler mailto: wolfgang.riss...@freenet.de mobil: +49 1520 9191759 - may the source be with you -
Access a newer Version of PGDB (v13) with an older libpq (v10 x86)
Hello community, we are (unfortunately) on windows with our DB and our application. Now we want to switch to a new version of Postgres, because we want to use logical replication (to replace our old Postgres 8.3 with slony-replication). The problem is, that our application (IDE MS-VisualStudio, C++) has to be 32bit, because of some old 32bit-dll's, which we cant kick out at the moment. So I compiled a libpqxx with the last 32bit libpq (which is v10). I tried to access a PostgresDB 64bit v10, and it worked very nice. So my question is, could (or should) we rather use PostgresDB v13 (to get the newest) or should we stay on PostgresDB v10 (to be more compatible to our libpq/libpqxx). Thank you, woelfchen72 -- - may the source be with you -
Re: ALTER ROLE ... SET in current database only
Abdul Qoyyuum: Wouldnt you need to connect to the database first before you can ALTER ROLE anything? Of course, otherwise the notion of "current database" wouldn't make sense at all. But that's only before executing the code. I am not writing and executing this code at the same time. In my case I'm seeding a postgres docker container with settings and data on first launch. The database name is passed to the container via environment variable. But, I'm sure there are other use-cases where code should be written once, but be executed in different databases. Best, Wolfgang
ALTER ROLE ... SET in current database only
Hi, I'm trying to set a GUC for a role in the current database only - but don't know the name of the database at the time of writing code. Could be development, staging, ... I would basically like to do something like this: ALTER ROLE a IN CURRENT DATABASE SET b TO c; Although that syntax doesn't exist (yet). I think I could wrap it in a DO block and create the statement dynamically. Alternatively, I could probably INSERT INTO / UPDATE pg_db_role_setting manually? Any other ideas how to achieve this easily? Does the proposed "IN CURRENT DATABASE" syntax sound useful to anyone else? Best, Wolfgang
Re: Execution order of CTEs / set_config and current_setting in the same query
Tom Lane:
I think you're on fairly shaky ground here. Generally speaking, a CTE
will be executed/read only when the parent query needs the next row from
it. Your examples ensure that the CTE is read before the parent query's
results are computed; but in realistic usage you'd presumably be joining
the CTE with some other table(s), and then the execution order is going
to be a lot harder to predict. This approach is also going to
fundamentally not work for settings that need to apply during planning
of the query (which, notably, probably includes "role").
Ok, thanks for confirming that.
You'd be far better off to investigate ways to send SET LOCAL first,
without incurring a separate network round trip for that. If you're
using simple query mode that's easy, you can just do
res = PQexec("SET LOCAL ... ; ");
In extended query mode you can't get away with that, but you might be able
to issue the SET LOCAL without immediately waiting for the result.
Yes, that's what we did so far. We switched to set_config to parametrize
the query.
Is there any way to not wait for a SELECT? I don't care about the
resultset, so I need something like PERFORM but for SQL, not plpgsql, I
think?
Execution order of CTEs / set_config and current_setting in the same query
Hi,
with PostgREST [1] we are translating HTTP requests into SQL queries.
For each request we are setting some metadata (headers, ...) as GUCs.
We used to do it like this:
SET LOCAL request.headers.x = 'y';
...
Since this is user-provided data, we want to use parametrized/prepared
statements. This is not possible with SET, so we switched to:
SELECT set_config($1, $2, true), ...;
Both these queries are preceding our main query. The SELECT set_config
is a bit slower than the SET LOCAL, probably because of more overhead on
the SELECT.
Now, we are wondering: To reduce overhead, can we move the set_config
calls to a CTE as part of the main query? The values would need to be
available with current_setting(...) in the remaining query.
Of course we would need to ensure execution order, so that this CTE will
always be fully executed, before all the other parts of the query.
Is this possible to do?
We did some basic testing, that seemed to be successful:
WITH set AS (
SELECT set_config('pgrst.hello', 'world', true)
)
SELECT current_setting('pgrst.hello')
FROM set;
or
WITH set AS (
SELECT set_config('pgrst.hello', 'world', true)
),
main AS (
SELECT
current_setting('pgrst.hello') AS hello,
FROM set,
)
SELECT
current_setting('pgrst.hello'),
main.hello,
FROM set, main, ;
Queries like this seem to have set the GUC correctly. But is this
guaranteed? What would need to be done to guarantee it?
I have a feeling that even though this works for those simple cases,
there is some risk involved...
Additional question: If this can be guaranteed - what about using
set_config('role', 'xxx', true) in the same way? Putting this into those
examples above and checking with CURRENT_USER seems to work as well. How
likely would this lead to problems with privileges / permissions?
Any input/insight would be helpful.
Thanks
Wolfgang
[1]: https://postgrest.org
pg/tcl performance related
Hi,
suppose I want to create a tcl function that basically walks through a long
list and compares its
argument against all entries
In plain tcl I would probably write
set data {
# lots of stuff here
}
proc check val {
global data
foreach d $data {
# this does the work
}
}
Now, for pl/tcl, I could use either
create function check(text) returns text as $$
set data {
# the data value
}
foreach d $data {
# the work
}
$$ language pltcl;
or
create function check(text) returns text as $$
if ![info exists GD(data)] {
set GD(data) {
# the data value
}
}
foreach d $GD(data) {
# the work
}
$$ language pltcl;
Does this make any difference - i.e. how often will parsing the data happen?
Best regards
Wolfgang Hamann
