Re: query_to_xml() returns invalid XML when query returns no rows

uld write select query_to_xml('select 42 where false', false, false, ''); -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: query_to_xml() returns invalid XML when query returns no rows

On 3/13/18 15:21, Thomas Kellerer wrote: > I still think it's incorrect to return an empty (=invalid) XML instead of a > NULL value though. This behavior is specified in the SQL standard. While an empty string is not a valid XML "document", it is valid as XML "conte

Re: Problem with connection to host (wrong host)

> > So a bit confused, is psql ignoring the host parameter .3 is the host you are connecting to, as seen from the client. .2 is the host your connection is coming from, as seen from the server. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: LDAP Bind Password

purposes. We want to either encrypt it or > authenticate without binding. Any insights into this is appreciated. You can use the "simple bind" method that is described in the documentation. That one doesn't involve a second bind step. -- Peter Eisentraut http://w

Re: List all columns referencing an FK

re still pieces missing in the standard holding this back? I think you'll still have the same problems if the same constraint name appears more than once per schema. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: How to monitor logical replication initial sync?

me = 'mysub'); The key is checking the srsubstate column for 'r' (ready). -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Can we run pg_basebackup master is still working normally (selects, updates, deleted, etc)

generated during the backup and replaying it on the newly created standby. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Can I disable sslcert/sslkey in pg_service.conf?

error: tlsv1 alert unknown ca > > I tried the opposite of moving the .postgresql directory to a different > name and putting a hard coded certificate path in pg_service, but it > looks to have its own sets of challenges. I think that's probably the best way out, though. -- Pet

Re: How to create logical replication slot with NOEXPORT_SNAPSHOT in jdbc

using jdbc? That doesn't do what you want. You still need to wait for the snapshot to be created; there is no way around that. The NOEXPORT_SNAPSHOT option just means that the snapshot, once created, won't be exported for use by other sessions. -- Peter Eisentraut

Re: User documentation vs Official Docs

l against any of the other tool chains that have been mentioned. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: scram-sha-256 authentication broken in FIPS mode

lternative OpenSSL versions out there that defy any documentation. Of course, we should also see if this actually fixes the reported problem. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: pglogical extension. - 2 node master <-> master logical replication ?

this issue in the working subscription direction. The problem might be in the dsn that you gave to create_node(). Hard to tell without a fully reproducible script. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Default ordering option

on the > scan and join plan types and the order on disk, but it must not be > relied on. > > > I would like to know if there is any way to change that to have a "real" > random behaviour. It might be an interesting exercise to implement this as a post-parsing ho

Re: Default ordering option

nt that? Look for post_parse_analyze_hook. Walk the parsed query tree, look for queries without ordering clause and manufacture one. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Changing work_mem

On 2019-08-13 17:16, rihad wrote: > If I increase it in postgresql.conf and SIGHUP the master server, will > the change be applied to all running backends, or only to the ones > started after the change? Thanks. It will be applied to all running backends. -- Peter Eisentraut

Re: question about zeroes in the wal file names

fully support 64-bit integers, and the LSNs and the files were tracked internally as pairs of 32-bit integers. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: pg_receivexlog or archive_command

ces of pg_receivexlog running and copying things to different places. This is complicated to do correctly with archive_command. 2) pg_receivexlog will fsync the files it writes. This is also complicated to do correctly with archive_command. -- Peter Eisentraut http://www.2ndQuadra

Re: Postgres 12: backend crashes when creating non-deterministic collation

seem to mean that it's ICU's bug not ours. Some build farm coverage of Windows+ICU would be nice. We have test cases in place that might have caught this. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: logical replication - negative bitmapset member not allowed

simplified your reproduction steps from the previous message to a test case, and I can confirm that your proposed fix addresses the issue. A patch is attached. Maybe someone can look it over. I target next week's minor releases. -- Peter Eisentraut http://www.2ndQuadran

Re: logical replication - negative bitmapset member not allowed

useful in the test case? Turns out it's not necessary. Attached is an updated patch that simplifies the test even further and moves it into the 008_diff_schema.pl file. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Tra

Re: logical replication - negative bitmapset member not allowed

On 2019-11-05 17:18, Andres Freund wrote: On 2019-11-05 16:02:51 +0100, Peter Eisentraut wrote: $node_publisher->stop('fast'); + + +# TODO: https://www.postgresql.org/message-id/flat/a9139c29-7ddd-973b-aa7f-71fed9c38d75%40minerva.info + +$node_publisher = get_new_nod

Re: logical replication - negative bitmapset member not allowed

On 2019-11-07 16:18, Jehan-Guillaume de Rorthais wrote: On Thu, 7 Nov 2019 16:02:21 +0100 Peter Eisentraut wrote: On 2019-11-05 17:05, Jehan-Guillaume de Rorthais wrote: I have simplified your reproduction steps from the previous message to a test case, and I can confirm that your proposed

Re: upgrade and migrate

e way to go, since pg_upgrade doesn't support 8.3. Also consider Londiste. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Identity columns, DEFAULT keyword and multi-row inserts

code then maybe. It certainly seems a bit inconsistent. It looks like the multi-row case in transformInsertStmt() would have to develop a bit more smarts to discover this case and then replace the RTE reference in the target list with a single SetToDefault node? -- Peter Eisentraut

Re: Logical Replication of Multiple Schema Versions

nd the new type. You really need to carefully plan and test each class of scenarios separately. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Logical replication DNS cache

something  - The OS underlying amazon's RDS service Postgres itself doesn't cache any host name resolution results. I don't know about the other two pieces. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Max locks

peak). max_locks_per_transactions only affects relation locks (also known as heavy weight locks), but pg_locks also shows other kinds of locks. Filter by locktype = 'relation' to get the appropriate view. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Developmen

Re: Is it safe to transfer logical replication publication/subscription?

tion.html#LOGICAL-REPLICATION-SUBSCRIPTION-SLOT for details. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Is it safe to transfer logical replication publication/subscription?

tions on a remote host. I'll read some more about the replication slots themselves (I did read about them a while back), but doing the above seems like a good way to break B from A, before resubscribing C to A instead? Yes, that's the one you want. -- Peter Eisentraut

Re: Reading WALs

each other. So you can have a logical replication stream and wal2json next to each other, and they won't interfere with each other. So what you are considering is possible and safe. (I don't know whether it will give you satisfactory insights.) -- Peter Eisentraut

Re: Mixed Locales and Upgrading

when selecting data that is not representable as LATIN1 or whatever. However, if you change the setting, that doesn't mean your terminal setup will actually display Unicode correctly. You said you're dealing with mostly ASCII-ish data anyway, so it will probably not make a differe

Re: PG12.2 Configure cannot enalble SSL

of the test failure. (Search the file for "openssl/ssl.h" to find the right place.) -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Seamless Logical Replication during Fail-over

part of PostgreSQL 9.6 release. But I am not sure if these changes made it to any of the releases. You are right that the fail-over slot mechanism was supposed to address this but it never made it into a release. I'm not aware of an open-source solution for this right now. -- Peter Eisen

Re: Logical replication troubles

Are there error messages shown from commands or in the server logs? Is replication progressing, but doing something your are not expecting? etc. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Logical replication troubles

ding. So when the subscription worker connects, it initially sees a state as of the creation of the replication slot, when the publication did not exist yet. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Logical replication troubles

best. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Postgresql 9.6 -> AWS RDS Postgresql 12.2 with pg_logical

ce of advice would likely be to upgrade. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Logical Replication and table bloat

arate vacuum tuning necessary for this, but if you are experiencing issues, first treat it like a normal vacuum configuration problem. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Logical replication stuck in catchup state

root cause for that. Have you checked the server logs? Maybe it has trouble applying a change, for example due to a unique constraint or something like that. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: pgbouncer bug?

1.13 and 1.14 It could be related to the SCRAM pass-through. Greig, if you have a way to reproduce it, please file a complete bug report on GitHub. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: SSL between Primary and Seconday PostgreSQL DBs

to stand up a 2 way SSL communication channel between the primary and secondary, or does anyone have one that they can share? Thanks,   Susan -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: SSL between Primary and Seconday PostgreSQL DBs

On 2020-09-03 14:01, Susan Joseph wrote: Unfortunately I am not allowed to use wireshark in my environment.  Good idea though The system view pg_stat_ssl, in combination with pg_stat_activity, will show you whether a connection is using SSL. -- Peter Eisentraut http://www

Re: PG13 partitioned table logical replication

tion about the table from the publisher, it is looking for a real table, which it doesn't find, because it's a partitioned table. So this combination doesn't work. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Call for translations

eneral discussion and coordination of translation activities. The time is now. Even though PostgreSQL 13 was just released, translation updates for the PostgreSQL 13 stable branch are still welcome. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support,

Re: Unable to compile postgres 13.1 on Slackware current x64

On 2020-11-16 18:30, Tom Lane wrote: In this case, I see one use of the constant TRUE in collationcmds.c, but I wonder how come that's there given that we deprecated upper-case TRUE some time ago. In 2eb4a831e5fb5d8fc17e13aea56e04af3efe27b4, I intentionally left that there because it was the d

Re: Set COLLATE on a session level

On 2020-11-20 08:13, Dirk Mika wrote: we come from the Oracle world and we have an application that, depending on a setting, sends the command ALTER SESSION SET NLS_SORT=... when connecting to the database. Is there a similar way to set a COLLATE for a session in PostgreSQL? I know that I can

Re: Set COLLATE on a session level

On 2020-12-04 17:18, Tom Lane wrote: There is a SET COLLATION command in the SQL standard that does this. Someone just has to implement it. It wouldn't be terribly difficult, I think. [ squint... ] Just because it's in the standard doesn't mean it's a good idea. It sounds like this is morall

Re: Error dydl : image not found when trying to install pg on Catalina

On 17.04.22 13:28, cecile rougnaux wrote: dyld: Library not loaded: /usr/local/opt/icu4c/lib/libicui18n.70.dylib Whenever libicu's major version changes, you need to rebuilt the postgresql package.

Re: ERROR: new collation (en_US.UTF-8) is incompatible with the collation of the template database (en_US.utf-8)

On 23.06.22 20:21, Adrian Klaver wrote: Not sure why that is necessary? Is seems this is low hanging fruit that could dealt with by the equivalent of lower('en_US.UTF-8') = lower('en_US.utf-8'). Well that was clear as mud. My point was that I don't see why the end user should have to do this

Re: ADD COLUMN ts tsvector GENERATED too slow

On 06.07.22 10:42, Florents Tselai wrote: I have a beefy server (40+ worker processes , 40GB+ shared buffers) and a table holding (key text, text text,) of around 50M rows. These are text fields extracted from 4-5 page pdfs each. I’m adding the following generated col to keep up with tsvectors

Re: unable to understand query result

On 06.07.22 11:31, Stefan Froehlich wrote: I have a database returing these result sets for two almost identical queries: #v+ $ select id, pid, length(pid), md5(pid) from product where pid like '800'; id | pid | length | md5 --+-++--

Re: Would it be possible to add functions to tab-completion in psql?

On 16.08.22 16:14, hubert depesz lubaczewski wrote: Specifically, I'd ask what is the harm of increasing what tab completion can do by a lot - for example, make it tab-complete fields from all tables. And make it possible to tab-complete column name anywhere in where clause. But function name in

Re: lippq client library and openssl initialization: PQinitOpenSSL()

On 11.09.22 23:43, Daniel Gustafsson wrote: On 11 Sep 2022, at 23:35, Tom Lane wrote: Daniel Gustafsson writes: On 11 Sep 2022, at 17:08, Tom Lane wrote: Don't believe so. The HAVE_CRYPTO_LOCK stuff is all obsolete and not compiled if you built against 1.1.0. The only thing left that will

Re: Postgres calendar?

On 04.10.22 23:02, Bruce Momjian wrote: Would people be interesting in subscribing to a Postgres calendar that includes dates for minor releases, final minor release dates for major versions, commit fests, and even Postgres events? Events already exists: https://calendar.google.com/calendar/ic

Re: Fwd: Postgre und GIS Umstellung von MD5 auf SCUM SHA 256

You can send questions in German to . On 23.11.22 11:22, Nikolas Hanry wrote: Liebe Community, Wir haben versucht, die Authentizierung  in unserer postgresql-Umgebung von MD5 zu SCRUM SHA 256 zu ändern (PostgreSQL V.11) - Stoppen Sie die Dienste und beenden alle aktiven Verbindungen - mit a

Re: Switching identity column to serial

On 04.02.23 21:55, Erik Wienhold wrote: Why doesn't this work? BEGIN; DROP SEQUENCE t_id; [This won't work, you need to use ALTER TABLE / DROP IDENTITY.] CREATE SEQUENCE new_t_id_seq AS INTEGER OWNED BY t.id; ALTER SEQUENCE new_t_id_seq OWNER TO new_owner; SELECT setval('new_t_id'

Re: Get the sequence name corresponding to a GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY column

On 07.02.23 11:43, Sebastien Flaesch wrote: select '"'||ns.nspname||'"."'||p.relname||'"' tabname, c.attname colname Just a side note: You might find the quote_ident() function useful.

Re: PostgreSQL optimizations for CoW FS

On 22.02.23 10:41, HECTOR INGERTO wrote: Let’s say we have to run a PostgreSQL instance on top of a copy on write filesystem like ZFS or BTRFS. In adittion to set full_page_writes = off, what other optimizations can be done on the PostgreSQL side? Look at the settings wal_init_zero and wal_rec

Re: Problem with stored procedure and nested transactions

actually trying to do, but you might need to reorganize it a bit. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: getting pg_basebackup to use remote destination

uld run pg_basebackup on the host where you want to set up your standby. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: oracle_fwd - is it safe or not?

fwd uses a kernel module? Are you sure? That is surprising. Perhaps he meant that the extension runs in the PostgreSQL "kernel" and could crash PostgreSQL, which is true of any extension. But oracle_fdw seems reasonably popular, so I would expect a base level of quality. -- Peter Eisen

Re: Channel binding not supported using scram-sha-256 passwords

VE_PGTLS_GET_PEER_CERTIFICATE_HASH into account before selecting SCRAM-SHA-256-PLUS. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Plpythonu extension

s removed here: https://github.com/Homebrew/homebrew-core/pull/36074 If you don't care about understanding that, maybe try this alternative tap: https://github.com/petere/homebrew-postgresql -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Suppo

Re: procedures and transactions

except maybe needing to retry after a failure) > and simply send `call foo(?, ?)` to the DB. You can run SET TRANSACTION ISOLATION LEVEL in a procedure. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: procedures and transactions

On 2019-02-20 17:45, Rob Nikander wrote: >> On Feb 20, 2019, at 10:07 AM, Peter Eisentraut >> wrote: >> >> You can run SET TRANSACTION ISOLATION LEVEL in a procedure. > > I tried that before but I get this error: > > create or replace procedure t_tes

Re: Channel binding not supported using scram-sha-256 passwords

ndif > + } Is that right? Won't we then just select nothing if the macro is not defined? -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Channel binding not supported using scram-sha-256 passwords

upports channel binding > (HAVE_BE_TLS_GET_CERTIFICATE_HASH). If the server does not support > channel binding, then only SCRAM is sent. After reading it again a few more times, I think your patch is correct. I tried reproducing the issue locally, but the required OpenSSL version is too old to be easily ava

Re: Channel binding not supported using scram-sha-256 passwords

custom-built OpenSSL, and I can confirm it works. > Another trick would be to comment out the sections in libpq where > HAVE_PGTLS_GET_PEER_CERTIFICATE_HASH is used to emulate a compilation > with OpenSSL 1.0.1 features and older, while still linking with > 1.0.2. Yeah, that might have bee

Re: python install location

uilding that we can tell postgres > which install of python to use when invoking plpython? No, that it determined at build time. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Property Graph Query Language proposed for SQL:2020

> * Property Graph Query Language (PGQL), an SQL-like query language for > graphs, including an Open-sourced parser and static query validator on > GitHub by Oracle. > http://pgql-lang.org/ Yes, I was just at this meeting this week: https://www.w3.org/Data/events/data-ws-2019/ -- Pet

Re: Case Insensitive

On 2019-03-28 09:20, Sridhar N Bamandlapally wrote: > Can we achieve CASE INSENSITIVE in PostgreSQL? Use the citext extension. In PostgreSQL 12, there will be support for case-insensitive collations. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 2

Re: logical replication - negative bitmapset member not allowed

an't quite reproduce this. There are various other checks that prevent this scenario, but it's plausible that with a bit of whacking around you could hit this error message. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Seeded Replication

; post  > https://www.depesz.com/2017/02/07/waiting-for-postgresql-10-logical-replication/ >  . I doubt you will get a huge speedup, since the dump and the initial data copy use the same COPY command internally. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development,

Re: Upgrading locale issues

On 2019-05-03 15:35, Daniel Verite wrote: > 'b' < 'a' is never true for any locale AFAIK, But there is 'bb' < 'aa' in Danish. :-) -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Refresh Publication takes hours and doesn´t finish

LATERAL pg_get_publication_tables(P.pubname) > + WHERE C.oid = pg_get_publication_tables.relid; No reason I think, just didn't quite manage to recognize the possibility of using LATERAL at the time. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: pg_repack issue

_repack] Error 1 > make[1]: Leaving directory `/home/postgres/software/pg_repack-1.4.4/bin' > make: *** [all] Error 2* It's a bit bogus that pg_repack would require this, but perhaps installing the readline-devel (or similar) package would get you past this. -- Peter Eisentraut

Re: ICU, locale and collation question

On 09.05.23 08:54, Oscar Carlberg wrote: Our initdb setup would then look like this for compatibility; -E 'UTF-8' --locale-provider=icu --icu-locale=sv-SE-x-icu --lc_monetary=sv_SE.UTF-8 --lc-numeric=sv_SE.UTF-8 --lc-time=sv_SE.UTF-8 --lc-messages=en_US.UTF-8 Should we still provide createdb wit

Re: ICU, locale and collation question

On 10.05.23 07:02, Kirk Wolak wrote: On Tue, May 9, 2023 at 11:24 AM Peter Eisentraut <mailto:peter.eisentr...@enterprisedb.com>> wrote: On 09.05.23 08:54, Oscar Carlberg wrote: > Our initdb setup would then look like this for compatibility; > -E 'UTF-8'

Re: function signature allow "default" keyword

On 15.05.23 10:33, jian he wrote: function idea. allow function calling using the default keyword for any of the input arguments. example: https://dbfiddle.uk/FQwnfdmm So something like this "SELECT * FROM customer_orders(2579927, 'order_placed_on DESC', default

Re: Proposal to Compile a 256-Byte Identifier Length Version Alongside the Current 64-Byte Version

On 10.10.23 08:22, Laurenz Albe wrote: Apart from that, it is a good idea to use table names that are standard SQL identifiers, so that you don't have to double quote them all the time. FWIW, the Chinese character sequences posted here would be valid unquoted identifiers if PostgreSQL implemen

Re: Partitioning, Identity and Uniqueness (given pg 16 changes)

On 19.02.24 04:32, Darryl Green wrote: I note that in Postgresql 16 identity column handling in partitioned tables has been aligned to the view that the partitioned table as a whole is a single relation (and so a unique identity across partitions). This makes sense. The change that I think yo

Re: Non-Stored Generated Columns

On 28.02.24 16:09, Dominique Devienne wrote: We use generated columns extensively. And we have foreign-keys attached to those generated columns. The fact they are always Stored thus wastes space in our case. Any chance PostgreSQL might gain actual virtual / non-stored generated columns soon? Eve

Re: (When) can a single SQL statement return multiple result sets?

On 11.04.24 01:02, Tom Lane wrote: And if not, why can't I write a stored procedure or function that returns multiple result sets? [ shrug... ] Lack of round tuits, perhaps. We don't have any mechanism today whereby a stored procedure could say "please ship this resultset off to the client, bu

Re: Identity column data type difference in PostgreSQL and Oracle

On 03.05.24 12:57, Muhammad Ikram wrote: Tables which have an identity column in Oracle when migrated to PostgreSQL, the data type of Identity column is changed to bigint from number by the tools. This causes the size of column to be reduced to max value supported by bigint which is way lower t

Re: When to REINDEX a serial key?

On 06.07.21 14:19, Ron wrote: On 7/6/21 4:52 AM, David Rowley wrote: On Tue, 6 Jul 2021 at 21:35, Ron wrote: The legacy RDBMS which I used to manage has a tool for analyzing (not in the Postgresql meaning of the word) an index, and displaying a histogram of how many layers deep various part

Re: Question about how to handle numeric (decimal) data types while using libpq

On 06.07.21 13:04, Sudheer H R wrote: I am trying to use libpq for interfacing with PostgreSQL from a C/C++ based application. I have tried to use binary format of data for both sending and receiving data to and from server (resultFormat = 1). As I understand most binary types, int, float etc

Re: pg_upgrade as a way of cloning an instance?

On 07.07.21 08:53, Luca Ferrari wrote: Hi all, someone pointed me out that pg_upgrade can be used to do a clone of the database, specifying the same binaries such as pg_upgrade -B /usr/pgsql-13/bin -b /usr/pgsql-13/bin -D /data/clone -d /data/src I tested it and it seems to work, even if I don'

Re: libicu global support

On 11.07.21 23:52, Jakub Jedelsky wrote: during the adoption of Centos 8 on our servers we ran into problems with Postgresql (13.3), glibc (delivered by the Centos) and performance of sorting. Because of that we're planning to use the ICU collations (en-x-icu), but the current implementation is

Re: pg_wal lifecycle

On 13.07.21 09:07, Luca Ferrari wrote: I'd like to see if I get it right about pg_wal: it grows up to pretty much max_wal_size, at which point a checkpoint is triggered. If the server triggers a timed checkpoint before the pg_wal is at max_wal_size, the system recycles the wals thus keeping the p

Re: sort order

On 27.07.21 19:07, Marc Millas wrote: so, obviously, both lc_collate knows about the é but obviously, too, they do behave differently on the impact of the beginning white space. I didn't see anything about this behaviour on the doc, unless the reference at the libc should be understood as ple

Re: lower() and unaccent() not leakproof

On 26.08.21 06:52, David G. Johnston wrote: On Wednesday, August 25, 2021, Christophe Pettus > wrote: lower() and unaccent() (and most string functions) are not marked as leakproof.  Is this due to possible locale / character encoding errors they might encou

Re: lower() and unaccent() not leakproof

On 26.08.21 10:40, Daniel Gustafsson wrote: On 26 Aug 2021, at 09:58, Peter Eisentraut wrote: On 26.08.21 06:52, David G. Johnston wrote: On Wednesday, August 25, 2021, Christophe Pettus mailto:x...@thebuild.com>> wrote: lower() and unaccent() (and most string functions) are not

Re: lower() and unaccent() not leakproof

On 26.08.21 16:00, Tom Lane wrote: Generally speaking, we've been resistant to marking anything leakproof unless it has a very small code footprint that can be easily audited. In particular, anything that shares a lot of infrastructure with not-leakproof functions seems quite hazardous. Even if

Re: Understanding the differences between Temporal tables, CDC and Time Series.

On 10.11.21 07:43, SQL Padawan wrote: I'm looking into Temporal Tables (TT - i.e. System/Application time versioning) but I would like to understand the differences between TTs and two other features (which appear similar in many ways) - Change Data Capture and Time Series. Are Temporal Table

Re: Issues cross-compiling libpq 14.x to MacOS armv8

On 30.11.21 22:04, Tom Lane wrote: Daniel Gustafsson writes: On 30 Nov 2021, at 20:59, Tom Lane wrote: AFAICS this is the only test in our configure script that is a hard fail when cross-compiling, and I don't see a reason for it to be that. We could just assume that /dev/urandom will be avail

Re: case insensitive collation of Greek's sigma

On 26.11.21 08:37, Jakub Jedelsky wrote: postgres=# SELECT postgres-# 'ΣΣ' ILIKE 'σσ' COLLATE "en_US", postgres-# 'ΣΣ' ILIKE 'σς' COLLATE "en_US" postgres-# ;  ?column? | ?column? --+--  t        | f (1 row) postgres=# SELECT postgres-# 'ΣΣ' ILIKE 'σσ' COLLATE "en-US-x-icu", po

Re: Are Foreign Key Disabled During Logical Replication Initial Sync?

On 06.12.21 15:50, Avi Weinberg wrote: Does it mean that populating each table is done in a single transaction?  If so, when I have tables with foreign keys between them, is it guaranteed that logical replication will populates the tables in the proper order so the foreign key will be enforced?

Re: Are Foreign Key Disabled During Logical Replication Initial Sync?

On 07.12.21 08:51, Avi Weinberg wrote: Just to clarify, they are disabled during initial sync only or are always disabled on subscriber side? Are all triggers disabled during initial sync or just foreign keys? All triggers are by default disabled on replicas. See the ALTER TABLE clauses DISA

Re: error connecting to pgbouncer admin console

On 07.12.21 17:10, Zwettler Markus (OIZ) wrote: I did a pgbouncer configuration using the following ini file: [databases] * = host=localhost port=5433 auth_user=pgbouncer Using the name "pgbouncer" for auth_user is buggy. Try using a different name.

Re: PGBouncer logs explanation required

On 19.12.21 12:50, Shubham Mittal wrote: 2021-11-25 14:45:45.244 IST [18307] LOG C-0x6ae270: sit/postgres@abcdlogin attempt: db=sit user=postgres tls=no 2021-11-25 14:45:45.299 IST [18307] LOG S-0x6b4f48: sit/postgres@abcd new connection to server (from abcd) 2021-11-25 14:46:17.843 IST [18307]

  1   2   >