Hi,
I've instrumented by libpq code, to show the byte size of the result-set.
And I'm suprised that it's never smaller than 2,264 bytes, then the next
size I see is 3,288, etc... Even when receiving no rows at all, in fact.
I've trimmed-out the SQL, but these are all SELECT statements.
The output
Hi.
It's my first time using COPY TO. And first time using built-in CSV support.
Performs well. BUT...
The code below (real code, but using a custom libpq wrapper lib) is run on
a few tables, with unit tests that verify the number of lines of the
output file.
And for a few of those tables, there'
On Fri, Mar 11, 2022 at 7:38 PM Daniel Verite wrote:
>> Dominique Devienne wrote:
> > These values are 'normal'. I'm not used to CSV, but I suppose such newlines
> > must be encoded, perhaps as \n, since AFAIK CSV needs to be 1 line per row,
> &
On Fri, Mar 11, 2022 at 8:32 PM Daniel Verite wrote:
> Dominique Devienne wrote:
> > so I can easily do that "\n" encoding myself, as a post-processing on
> > the buffer I get back.
>
> Alternatively, it might be easier to use the default TEXT format of
>
Made a typo, using } instead of ) to test a weird table name, and got
disconnected. Normal? --DD
ddevienne=> create table "t |" ( id int};
ddevienne(> );
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the re
On Thu, Mar 17, 2022 at 11:38 AM Pavel Stehule wrote:
>> Made a typo, [...], and got disconnected. Normal? --DD
> I don't see any problem on master
OK, good. Must have been fixed already then. Or was a fluke. Thanks
for checking. --DD
On Tue, Apr 12, 2022 at 12:17 PM Dmitry Igrishin wrote:
> I'm excited to announce Pgfe 2.0.0, an advanced and feature rich
> PostgreSQL driver written in C++.
Congrats on the release. I've been following your progress, and saw
good stuff in your commits.
Could you please more clearly state in th
LibPQ has various defaults for the host, user, and DB name.
There's also the password file, the service file and service name.
In the example below, I can connect with a "naked" psql invocation.
Once connected, can I find out all aspects of the connection string?
Or where they came from, like a pg
On Wed, May 18, 2022 at 12:07 PM Dominique Devienne wrote:
> LibPQ has various defaults for the host, user, and DB name.
> There's also the password file, the service file and service name.
> In the example below, I can connect with a "naked" psql invocation.
>
>
On Wed, May 18, 2022 at 5:43 PM David G. Johnston <
david.g.johns...@gmail.com> wrote:
> On Wed, May 18, 2022 at 3:08 AM Dominique Devienne
> wrote:
>
>> Once connected, can I find out all aspects of the connection string?
>> Or where they came from, like a pgpass.conf
Hi,
The doc is explicit about defaults for the password file:
>From https://www.postgresql.org/docs/current/libpq-pgpass.html
Linux: ~/.pgpass
Windows: %APPDATA%\postgresql\pgpass.conf
But for the service file OTOH, only the Linux default is documented:
From: https://www.postgresql.org/docs/curre
On Thu, May 19, 2022 at 10:17 AM Julien Rouhaud wrote:
> > Or is it instead a lack of symmetry in the implementations of these
> > two mechanisms?
>
> As far as I can see from the code it should be
> %APPDATA%\postgresql\.pg_service.conf
>
> Does this one work?
Yes it does! Thanks.
> There is a
On Fri, May 20, 2022 at 5:31 AM Julien Rouhaud wrote:
> On Thu, May 19, 2022 at 06:38:46PM -0400, Tom Lane wrote:
> > Julien Rouhaud writes:
> > Pushed, with some minor twiddling to make ... descriptions more alike.
> Thanks a lot!
And thank you both! --DD
On Tue, Jun 21, 2022 at 7:59 AM Laurenz Albe wrote:
> I am surprised that you choose to be fettered by the constraints of a generic
> API like ODBC.
> For me, that only makes sense if you want to be portable to different
> databases.
Ditto.
> I would use ... C API of libpq, or, .. C++ boilerpl
Hi. I'm struggling to delete databases because of grants to roles on
objects of those DBs.
These DBs can have a large'ish number of schemas, 100-300 is typical.
and define a bunch of ROLEs "specific" to those schemas. Normally "login user"
ROLEs are never granted explicit access to objects, instea
On Thu, Jul 7, 2022 at 3:52 PM Tom Lane wrote:
>
> Dominique Devienne writes:
> > Hi. I'm struggling to delete databases because of grants to roles on
> > objects of those DBs.
>
> > These DBs can have a large'ish number of schemas, 100-300 is typical.
>
Hi,
Is there a way to programmatically now how many rows an UPDATE did update?
I've read about [PQcmdTuples][1], but surely I shouldn't have to parse
that string, no?
For selects, I have [PQnTuples][2], but what to do on INSERT, UPDATE, DELETE?
Parse the result of PQcmdTuples myself??? If so, what
Hi. I'm using binary binds and results for DMLs, as well as for COPY.
So far, I've stayed within built-in scalar and array types, thus I
could hardcode the OIDs of values "in the type-system" (in C++, via
traits).
But I'd like to venture into extension (e.g. hstore) and custom
(enums, domain, etc.
On Wed, Oct 5, 2022 at 8:17 PM Tom Lane wrote:
> Laurenz Albe writes:
> > On Wed, 2022-10-05 at 16:38 +0200, Dominique Devienne wrote:
> > Yes, you have to use PQcmdTuples(), and you have to convert the string to
> > an integer.
> > But don't worry: the resul
On Fri, Oct 14, 2022 at 2:31 PM Laurenz Albe wrote:
> You use the #defines like TEXTOID for the built-in Oids, right?
I don't. I used
https://github.com/postgres/postgres/blob/master/src/include/catalog/pg_type.dat
as a reference.
I suspect that should be fairly stable, right? I have at least 2 o
On Fri, Oct 14, 2022 at 4:35 PM Tom Lane wrote:
> > I don't. I used
> > https://github.com/postgres/postgres/blob/master/src/include/catalog/pg_type.dat
>
> They're stable
Good to know, thanks.
> but writing magic numbers leads to unreadable code.
> Use the macros from catalog/pg_type_d.h.
OK.
On Fri, Oct 14, 2022 at 4:39 PM Daniel Verite wrote:
> Or a SELECT, or an UPDATE RETURNING, or a DELETE RETURNING.
> The code still need to look at PQcmdStatus() to learn which kind it is,
Thanks Daniel. This is exactly what I needed. --DD
PS: Now I wonder whether PQcmdTuples() points to the sam
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 va
On Tue, Oct 18, 2022 at 6:04 PM David G. Johnston
wrote:
> On Tue, Oct 18, 2022 at 8:53 AM Dominique Devienne
> wrote:
>> I'm surprised by the result for bit(3) and char, when calling
>> pg_column_size().
> The base type is what matters, if the length of the actual
On Tue, Oct 18, 2022 at 6:04 PM Tom Lane wrote:
> Dominique Devienne writes:
> > I'm surprised by the result for bit(3) and char
> > The doc does mention 5-8 bytes overhead, but I expected
> > those for varying bit, not fixed-sized bit typed values.
>
> Your exp
Hi. I'd like some advice storing blobs (millions of them), ranging
from very small, to large > 1GB.
I know about bytea versus lo, and I have probably read most of what's
out there about them :)
Upfront, I have to state that I'm not keen on lo, because of security
considerations. We store
blobs in
On Wed, Oct 19, 2022 at 12:17 PM Andreas Joseph Krogh
wrote:
> First advice, don't do it. We started off storing blobs in DB for “TX
> safety”
>
Not really an option, I'm afraid.
> , but backup/restore quickly became too cumbersome so we ended up moving
> all blobs out and only store reference
On Wed, Oct 19, 2022 at 1:00 PM Andreas Joseph Krogh wrote:
> Ok, just something to think about;
Thank you. I do appreciate the feedback.
> Will your database grow beyond 10TB with blobs?
The largest internal store I've seen (for the subset of data that goes
in the DB) is shy of 3TB.
But we are
On Wed, Oct 19, 2022 at 1:38 PM Andreas Joseph Krogh
wrote:
> There's a reason “everybody” advices to move blobs out of DB, I've learned.
>
I get that. I really do. But the alternative has some real downsides too.
Especially around security, as I already mentioned. That's why I'd like if
possibl
On Wed, Oct 19, 2022 at 3:01 PM Daniel Verite wrote:
> Dominique Devienne wrote:
> > the fact the lo table is unique for the whole database would allow
> > users to see blobs from any schema, as I understand it.
> Each large object has its own set of permissions. Thi
On Wed, Oct 19, 2022 at 3:05 PM Alvaro Herrera wrote:
> On 2022-Oct-19, Dominique Devienne wrote:
> > OTOH, lo has random access, which I also need...
>
> Generally speaking, bytea sucks for random access, because if a TOAST
> item is compressed, it has to be always read fro
On Wed, Oct 19, 2022 at 4:29 PM Daniel Verite wrote:
> In your case I would probably opt for bytea (as opposed to large
> objects), and slicing the blobs in the application in chunks
Thanks for the advice, and the valuable info on LO permissions. --DD
On Wed, Oct 19, 2022 at 5:30 PM Alvaro Herrera wrote:
> That's exactly what I was trying to say.
> If there's no compression, we don't read prior chunks.
Great to read that. I'll probably try to benchmark w/ and w/o
compression eventually.
Need to deal with other issues first, will take a while t
On Wed, Oct 19, 2022 at 5:05 PM Laurenz Albe wrote:
> On Wed, 2022-10-19 at 12:48 +0200, Dominique Devienne wrote:
> > On Wed, Oct 19, 2022 at 12:17 PM Andreas Joseph Krogh
> > wrote:
> > > First advice, don't do it. We started off storing blobs in DB for “TX
&g
On Thu, Oct 20, 2022 at 12:21 PM Laurenz Albe wrote:
> On Thu, 2022-10-20 at 10:32 +0200, Dominique Devienne wrote:
> > FWIW, when Oracle introduced SecureFile blobs years ago in v11, it
> > represented a leap forward in
> > performance, and back then we were seeing them be
On Mon, Nov 21, 2022 at 4:58 PM Tom Lane wrote:
> Kirk Wolak writes:
> > We have our team of Windows developers, leveraging PSQL. But honestly,
> > it feels crippled after using PSQL in Linux for any length of time. Losing
> > auto-complete sucks (multi-line query recall/editing is lost as we
On Mon, Nov 21, 2022 at 6:12 PM Kirk Wolak wrote:
> On Mon, Nov 21, 2022 at 11:01 AM Dominique Devienne
> wrote:
> > FWIW, I've been using https://github.com/arangodb/linenoise-ng for Linux
> > and Windows,
>
> I've look at linenoise and without tab/autocom
On Mon, Nov 28, 2022 at 1:37 PM yin@163.com wrote:
> on conflict(itemid) do update
> set value = excluded.value + new.value, cnt = excluded.cnt +1 where
> excluded.itemid = new.itemid;
OT, but isn't `where excluded.itemid = new.itemid` redundant, given
`on conflict(itemid)`?
I'm aski
On Thu, Dec 1, 2022 at 4:23 PM Tom Lane wrote:
> "David G. Johnston" writes:
> > On Thu, Dec 1, 2022 at 7:59 AM Martijn Tonies (Upscene Productions) <
> > m.ton...@upscene.com> wrote:
> >> Can you modify the server code to store the original body in proc.prosrc
> >> again? It would be very helpfu
On Thu, Dec 1, 2022 at 5:10 PM Laurenz Albe wrote:
> On Thu, 2022-12-01 at 16:38 +0100, Dominique Devienne wrote:
> > FWIW, we have a custom schema introspection and diff'ing ad-hoc framework,
>
> This is arguable, but my opinion is that this is not a robust way to
> do d
On Thu, Dec 1, 2022 at 6:41 PM Adrian Klaver wrote:
> On 12/1/22 09:24, Dominique Devienne wrote:
> > I guess is a DBA-versus-Developer point-of-view difference. --DD
>
> What this points to is that there are multiple ways to handle this, many
> external to the server itself. M
On Thu, Dec 1, 2022 at 8:09 PM Christophe Pettus wrote:
> > On Dec 1, 2022, at 11:05, Dominique Devienne wrote:
> > I see. Still, Oracle preserves SQL as-is. SQLite preserve SQL as-is.
> > Would be nice if PostgreSQL did too. That's all I'm saying.
>
> Since th
On Fri, Dec 2, 2022 at 1:37 PM Laurenz Albe wrote:
> Great; then go ahead and use those databases, if it is important for you.
Now come on. We all love PostgreSQL.
But that doesn't mean we can't disagree on some decisions.
Especially when you are a USER of PostgreSQL, not a DEV of it,
and it's t
On Thu, Dec 1, 2022 at 8:51 PM Tom Lane wrote:
> Do you really fail to see the contradictions in this? You want the
> database to preserve the original DDL, but you also want it to update
> in response to subsequent alterations. You can't have both those
Hi. I probably didn't express myself cor
On Wed, Jan 18, 2023 at 9:03 PM Jeremy Smith wrote:
>> How to do this with two tables connected via one foreign key, that is
>> explained a dozen times in Stackoverflow. But what if the tree is 50
>> tables and 120 foreign key columns?
>> It can be done. But probably not manually.
>>
>> So, as thi
On Mon, Jan 30, 2023 at 5:11 PM veem v wrote:
> CREATE TABLE test1_UUID ( id bigint,source_id varchar(36) PRIMARY KEY, Name
> varchar(20) );
Maybe if you used a "native" 16-byte uuid, instead of its textual
serialization with dashes (36 bytes + length overhead), the gap would
narrow.
Copying the list...
-- Forwarded message -
From: Dominique Devienne
Date: Fri, Feb 3, 2023 at 4:57 PM
Subject: Re: Sequence vs UUID
To: veem v
On Thu, Feb 2, 2023 at 8:47 PM veem v wrote:
> Tested the UUIDv7 generator for postgres as below.
> With regards to perfo
0
┌───┐
│ count(randomblob(16)) │
├───┤
│ 100 │
└───┘
Run Time: real 0.278 user 0.250000 sys 0.00
> On Fri, 3 Feb 2023 at 21:28, Dominique Devienne
> wrote:
>
>> Something's of
On Tue, Feb 7, 2023 at 3:47 PM Merlin Moncure wrote:
> On Mon, Feb 6, 2023 at 1:22 PM Peter J. Holzer wrote:
>
>> On 2023-02-06 20:04:39 +0100, Julian Backes wrote:
>> But UUIDs are random and that plays havoc with locality.
>
>
> This is really key. [...] the databases I've seen that are writt
Hi. I'm requesting advice on something I'm about to start on.
In a normally 2-tier system, where "desktop" native apps connect directly
to PostgreSQL to access data, some things must be mediated via a
middle-tier service (i.e. 3-tier architecture). That service is HTTP based
thus clients (the "des
Hi. We are implementing an API which takes a list of row keys, and must
return info about those rows. To implement that efficiently, in as few
round-trips as possible, we bind a (binary) array of keys (ints, uuids, or
strings) and that works great, but only if the key is a scalar one.
Now we'd lik
On Thu, Feb 9, 2023 at 4:46 PM David G. Johnston
wrote:
> On Thu, Feb 9, 2023 at 8:30 AM Dominique Devienne
> wrote:
>
>> That's basically my plan. Now come the questions:
>> 1) will updating a row every second (for example) create issues?
>> 2) if yes to #1 abov
On Thu, Feb 9, 2023 at 4:51 PM David G. Johnston
wrote:
> On Thu, Feb 9, 2023 at 8:41 AM Dominique Devienne
> wrote:
>
>> Now we'd like to do the same for composite keys, and I don't know how to
>> do that.
>>
>
> An array-of-composites is simply:
On Thu, Feb 9, 2023 at 5:03 PM Dominique Devienne
wrote:
> On Thu, Feb 9, 2023 at 4:51 PM David G. Johnston <
> david.g.johns...@gmail.com> wrote:
>
>> On Thu, Feb 9, 2023 at 8:41 AM Dominique Devienne
>> wrote:
>>
>>> Now we'd like to do the s
On Thu, Feb 9, 2023 at 5:05 PM Adrian Klaver
wrote:
> On 2/9/23 07:30, Dominique Devienne wrote:
> > In a normally 2-tier system, where "desktop" native apps connect
> > directly to PostgreSQL to access data, some things must be mediated via
> > a middle-tier se
On Thu, Feb 9, 2023 at 5:37 PM David G. Johnston
wrote:
> On Thu, Feb 9, 2023 at 9:28 AM Alban Hertroys wrote:
>
>> > On 9 Feb 2023, at 16:41, Dominique Devienne
>> wrote:
>> > Now we'd like to do the same for composite keys, and I don't know how
>&
On Thu, Feb 9, 2023 at 5:51 PM Adrian Klaver
wrote:
> On 2/9/23 08:16, Dominique Devienne wrote:
> > On Thu, Feb 9, 2023 at 5:05 PM Adrian Klaver The flip side of that is that with known ports it would it easier to
> have a process on the Postgres machine or in the database that
Hi. Porting a unit test to PostgreSQL, we got a failure related to ordering.
We've distilled it to the below. The DB is en_US.UTF-8, and the sorting we
get
does not make sense to me. The same prefix can be sorted differently based
on the suffix apprently, which doesn't make any sense to me.
Surel
On Tue, Feb 14, 2023 at 11:23 AM Laurenz Albe
wrote:
> On Tue, 2023-02-14 at 10:31 +0100, Dominique Devienne wrote:
> > Hi. Porting a unit test to PostgreSQL, we got a failure related to
> ordering.
> >
> > We've distilled it to the below. The DB is en_US.UTF-8, and
On Tue, Feb 14, 2023 at 12:35 PM Alvaro Herrera
wrote:
> On 2023-Feb-14, Dominique Devienne wrote:
> > Honestly, who expects the same prefix to sort differently based on what
> > comes after, in left-to-right languages?
>
Look, we don't define the collation rules.
&
Hi. This is a bit unusual. We have a foreign key between two tables, with
ON DELETE CASCADE, to preserve referential integrity. But we apparently
also need to preserve the severed reference (by natural key, i.e. its
name), to later on reconnect the two entities after-the-fact, should the
parent row
On Thu, Feb 16, 2023 at 5:59 PM Adrian Klaver
wrote:
> On 2/16/23 08:55, David G. Johnston wrote:
> > On Thu, Feb 16, 2023 at 9:46 AM Adrian Klaver > <mailto:adrian.kla...@aklaver.com>> wrote:
> >
> > On 2/16/23 05:23, Dominique Devienne wrote:
> >
Hi. I have a large "legacy" code base that write information necessary for
Row-Level-Security in a highly denormalized custom had-hoc text format for
values, in key-value pairs in a table, which would be either impossible or
too slow to base any RLS policy on. The values are basically lists or maps
On Thu, Feb 16, 2023 at 6:58 PM Adrian Klaver
wrote:
>
> "... which would be either impossible or too slow to base any RLS policy
> on."
>
> and
>
> "At time point, changing the legacy code base is not really an option..."
>
> seem to be at odds.
>
I don't see why you say that.
So is the curren
On Thu, Feb 16, 2023 at 6:51 PM Adrian Klaver
wrote:
> You have two tables with list in their name, so are rows deleted from
> both. Just to be clear enity_list should actually be entity_list?
>
> Also how are entity and enity_list related?
>
I have to confess that your questions surprise me a b
On Thu, Mar 2, 2023 at 10:08 AM Laurenz Albe
wrote:
> On Thu, 2023-03-02 at 15:53 +0800, Navindren Baskaran wrote:
> If the other column is updated, it depends. If the updated column is not
> indexed and there is enough room for the new row version in the same
> table block, the index doesn't ha
Hi. Perhaps I missed it in the doc (e.g. [1]), but are DDLs around ROLEs
and GRANTs transactional?
Since I'm creating many ROLEs and making many GRANTs, based info I read
from PostgreSQL itself (in pg_catalog and elsewhere), should everything be
in a single transaction?
FWIW, I come from Oracle (
On Mon, Mar 6, 2023 at 4:06 PM Tom Lane wrote:
> Dominique Devienne writes:
> > Hi. Perhaps I missed it in the doc (e.g. [1]), but are DDLs around ROLEs
> > and GRANTs transactional?
>
> Your expectation is set wrongly.
Thanks for the precision Tom.
Although I'm
On Mon, Mar 6, 2023 at 3:02 PM Erik Wienhold wrote:
> > On 06/03/2023 14:19 CET Dominique Devienne wrote:
> > Perhaps I missed it in the doc (e.g. [1]), but are DDLs around ROLEs and
> > GRANTs transactional?
>
> Have you tried?
Nope. I thought about it, but then I cou
On Mon, Mar 6, 2023 at 4:31 PM Tom Lane wrote:
> Dominique Devienne writes:
> > On Mon, Mar 6, 2023 at 4:06 PM Tom Lane wrote:
> >> DDL commands in Postgres are
> >> transactional unless their man page explicitly says they're not.
>
> > Could you
Hi. I've recently realized via a post (or article?) from Laurenz that the
PUBLIC role has CREATE privilege on the 'public' schema by default (see
query below). I guess it can't be avoided?
OK, then I'll REVOKE that privilege when creating a new DB.
Like I already revoked the default CONNECT to PUB
On Thu, Mar 9, 2023 at 2:13 PM Laurenz Albe
wrote:
Hi,
On Thu, 2023-03-09 at 10:34 +0100, Dominique Devienne wrote:
> > Hi. I've recently realized via a post (or article?) from Laurenz that
> the PUBLIC
> > role has CREATE privilege on the 'public' schema by defa
I'm interested in the members of specific roles, providing the roles of
interest to the query via an array of integers (binary bind in code, not
textual array literal like I had to use to have the EXPLAIN work, see
below).
I figured that query would use the "pg_auth_members_role_member_index"
inde
On Mon, Mar 13, 2023 at 2:53 PM Tom Lane wrote:
> regards, tom lane
>
Thank you very much Tom. Very informative.
On Mon, Mar 13, 2023 at 2:53 PM Tom Lane wrote:
> > PS: libpq uses unsigned int for Oid. So do OIDs go above 2GB in practice?
>
> Yes, eventually.
>
OK, I've added support for native OIDs values (i.e. unsigned int,
sizeof(4)) in my libpq wrapper.
Tested with binary binding and getting of scalar
On Thu, Mar 16, 2023 at 6:48 AM Laurenz Albe
wrote:
> As we wrote, some of us think that cursors are useful, and we tried to
> explain why we think that. If you don't think that cursors are useful,
> don't use them. We are not out to convince you otherwise.
>
Perhaps OT (I only skimed this thr
On Thu, Mar 16, 2023 at 9:23 AM Pavel Stehule
wrote:
> čt 16. 3. 2023 v 9:18 odesílatel Dominique Devienne
> napsal:
>
>> [...] depends on what you value in a particular situation, latency or
>> throughput. --DD
>>
>
> cursors are optimized for minimal cost of
On Thu, Mar 16, 2023 at 11:15 AM Pavel Stehule
wrote:
> čt 16. 3. 2023 v 9:55 odesílatel Dominique Devienne
> napsal:
>
>> On Thu, Mar 16, 2023 at 9:23 AM Pavel Stehule
>> wrote:
>>
>>> čt 16. 3. 2023 v 9:18 odesílatel Dominique Devienne
>>> napsal
Hi. I'm surprised, I thought ACLs would never be empty for a database.
Does that mean nobody can connect to this database?
I guess SUPERUSER and/or its datDBA can?
What does a NULL AclItem[] mean exactly?
(BTW, datallowconn is true)
Thanks, --DD
ddb=> select datname, datdba::regrole::text from pg
On Mon, Mar 20, 2023 at 1:18 PM Erik Wienhold wrote:
> > On 20/03/2023 11:52 CET Dominique Devienne wrote:
> > What does a NULL AclItem[] mean exactly?
>
> It means that the object has default privileges (before any GRANT or REVOKE
> is executed). For databases this mean
On Mon, Mar 20, 2023 at 2:18 PM Erik Wienhold wrote:
> > On 20/03/2023 13:50 CET Dominique Devienne wrote:
> >
> > On Mon, Mar 20, 2023 at 1:18 PM Erik Wienhold wrote:
> > > > On 20/03/2023 11:52 CET Dominique Devienne
> wrote:
> > > > What does a
We have a query returning 1 row per constraint column,
which until recently we didn't realize wasn't preserving order of the
columns.
A colleague fixed that, with something like below:
SELECT ...
FROM pg_catalog.pg_constraint cnstr
...
CROSS JOIN LATERAL UNNEST(cnstr.conkey) WITH ORDINALITY AS co
On Thu, Mar 23, 2023 at 4:20 PM Adrian Klaver
wrote:
> On 3/23/23 04:12, Dominique Devienne wrote:
> > CROSS JOIN LATERAL UNNEST(cnstr.conkey) WITH ORDINALITY AS cols(value,
> rank)
> > ORDER BY cols.rank
> A before coffee solution:
>
Thanks for answering Adrian. An
On Fri, Mar 24, 2023 at 11:51 AM Alvaro Herrera
wrote:
> On 2023-Mar-23, Dominique Devienne wrote:
> > But I'm wondering about getting 1 row per constraint instead,
> > and fetching an array of column names.
>
> IIRC you can use array_agg(unnest ORDER BY ordinality), gr
On Fri, Mar 24, 2023 at 3:50 PM Jeffrey Walton wrote:
> On Fri, Mar 24, 2023 at 7:07 AM Inzamam Shafiq
> > Can someone please list pros and cons of MariaDB vs PostgreSQL [...]
> >
>
> [...] MySQL has over [1700 CVEs going back to 1999.][1]
> In contrast, PostgreSQL has about [240 CVEs going back
On Fri, Mar 24, 2023 at 5:40 PM Adrian Klaver
wrote:
> On 3/24/23 03:28, Dominique Devienne wrote:
> > On Thu, Mar 23, 2023 at 4:20 PM Adrian Klaver > <mailto:adrian.kla...@aklaver.com>> wrote:
> Your original question was:
>
> "But I'm wondering ab
Hi. Given the classical parent/child schema below, with an ON DELETE
CASCADE FK constraint, I'd like to know which index is used (if any) to
delete child rows affected by the CASCADE.
But explain (analyze) does not give me that.
The reason I'm asking, is because we currently (automatically) add i
Hi again,
I just sent a question regarding parent/child and cascading FKs.
But in reality, our schema has not 2 but 3 "layers",
with an additional grandchild "leaf" table (see below).
Given that many acces patterns are parent-based, i.e. get all
child of given parent, or get all grandchild of giv
On Tue, Mar 28, 2023 at 3:23 PM Tom Lane wrote:
> Dominique Devienne writes:
> > Hi. Given the classical parent/child schema below, with an ON DELETE
> > CASCADE FK constraint, I'd like to know which index is used (if any) to
> > delete child rows affected by the
On Tue, Mar 28, 2023 at 5:08 PM Peter J. Holzer wrote:
> On 2023-03-28 07:26:47 -0700, Adrian Klaver wrote:
> > On 3/28/23 06:17, Dominique Devienne wrote:
> > > PS: At this point, I don't even know how much cluster affects
> performance.
>
> I think that t
On Tue, Mar 28, 2023 at 6:06 PM Ron wrote:
> You can only get from parent to grandchild via child.id to
> grandchild.parent, so why not cluster grandchild on grandchild.parent?
>
Hi. I don't understand your question. Yes, of course, if I want all
grand-children of a given parent, I'll do for exa
Hi. Selecting/fetching an empty 1D array using a binary-mode PGresult,
gives me back 12 bytes, and the first 4, the rank, is 0, something I was
not expecting.
I was expecting dims_rank = 1, then first_dim = 0
Normal? Next two ints are kinda useless given the 0 rank?
It's easy to fix, to support ra
On Wed, Mar 29, 2023 at 3:45 PM Tom Lane wrote:
> Dominique Devienne writes:
> > Hi. Selecting/fetching an empty 1D array using a binary-mode PGresult,
> > gives me back 12 bytes, and the first 4, the rank, is 0, something I was
> > not expecting.
>
> Yeah, empty
On Wed, Mar 29, 2023 at 9:23 PM Adrian Klaver
wrote:
> On 3/29/23 12:11, Sebastien Flaesch wrote:
> > Oh the use of default keyword is new to me, thanks for that.
> >
> > But to make PostgreSQL more Informix-compatible,
> > zero should have been considered as well.
Perhaps.
> 1) Why? Down the
On Thu, Mar 30, 2023 at 11:42 AM Francisco Olarte
wrote:
> On Thu, 30 Mar 2023 at 10:01, Dominique Devienne
> wrote:
> > BTW, default and 0 are not the same thing. You cannot bind "default" in
> place of
> > an integer-valued prepared-statement placeholder, in a
Hi,
I already use arrays fine (e.g. text[]) in BINARY mode binds (with prepared
statement or not).
I already use COPY FROM STDIN BINARY fine, with scalar values.
But for the 1st time, I'm combining COPY with arrays,
and it is NOT fine yet :(
#08P01: ERROR: insufficient data left in message
CONT
On Thu, Mar 30, 2023 at 6:00 PM Tom Lane wrote:
> Dominique Devienne writes:
> > Does the wire-format of arrays differ between binary-binds and
> binary-copy?
>
> No.
>
An update on this, I think I figure it out, by comparing with COPY TO
STDOUT WITH (FORMAT BINARY).
I
My goal is to have clients connect to PostgreSQL,
and call a function that return a JWT token.
The JWT is supposed to capture the user (login role),
and the current_role (which has meaning in our app),
and sign it using a secret corresponding to a mid-tier
service the client will connect to later.
First, let me say I was holding off replying/thanking everyone to have the
time
to properly test this. Erik's quasi-question makes me break that silence.
On Tue, Apr 4, 2023 at 10:46 AM Erik Wienhold wrote:
> > On 04/04/2023 07:55 CEST walt...@technowledgy.de wrote:
> > For me, checking whether
Hi.
We are switching a schema type's surrogate/primary key, from `uuid` to
`int`.
That schema has parent-child relationships enforced with foreign-keys.
Our ingestion/schema-loading code uses COPY FROM STDIN BINARY.
Before, the SK/PK was generated client-side, as random uuid.
The ingestion code m
1 - 100 of 303 matches
Mail list logo
