>
> If the behavior of RETURNING is meant to be identical to that of simply
>> applying a cast, is there any actual advantage in using JSON_VALUE with
>> RETURNING? In other words, why not just do JSON_VALUE(json '"AQID"',
>> '$')::bytea instead of using RETURNING? I thought the point was precisely
> > For whatever it's worth, I'll note that SQL Server's OPENJSON does do
> > this (so when a JSON string property is extracted as a binary type,
> > base64 encoding is assumed). Other databases also have very specific
> > documented conversion rules for JSON_VALUE RETURNING (Oracle > docs.oracle.
>
>
>> SELECT JSON_VALUE(jsonb '"AQID"', '$' RETURNING bytea); -- Expected
>> 0x010203, got AQID
>>
>
> I get \x41514944 which is precisely what I would expect since it what this
> query results in as well:
>
> select 'AQID'::bytea;
>
If the behavior of RETURNING is meant to be identical to that o
Greetings hackers,
The de-facto standard for storing binary data in JSON documents seems to be
base64-encoded strings, so I was expecting JSON_VALUE's RETURNING bytea to
do base64 decoding. However, that does not seem to be the case:
SELECT decode('AQID', 'base64'); -- 0x010203
SELECT JSON_VALUE(
On Mon, Sep 18, 2023 at 1:31 PM Jelte Fennema-Nio wrote:
> Furthermore caching RowDescription is also not super useful, most
> clients request it every time because it does not require an extra
> round trip, so there's almost no overhead in requesting it.
Just to point out, FWIW, that the .NET N
>> Is there a missing line in the operator precedence table in the docs?
>
> I think the big question is whether AT TIME ZONE is significant enough
> to list there because there are many other clauses we could potentially
> add there.
Just to give more context, I'm a maintainer on Entity Framework
Greeting hackers,
In the operator precedence table[1] table, AT TIME ZONE isn't explicitly
listed out; that means it's to be interpreted in the "any other operator
category".
However, it seems that the precedence of AT TIME ZONE is actually higher
than that of the addition operator:
-- Fails wit
> > CREATE COLLATION some_collation (LC_COLLATE = 'en-u-ks-primary',
> > LC_CTYPE = 'en-u-ks-primary',
> > PROVIDER = icu,
> > DETERMINISTIC = False
> > );
> >
> > This works on PG14, but on PG15 it errors with 'parameter "locale" must
> > be specified'.
> >
> > I wanted to make sure
Hi all,
Testing on the PG15 beta, I'm getting new failures when trying to create a
collation:
CREATE COLLATION some_collation (LC_COLLATE = 'en-u-ks-primary',
LC_CTYPE = 'en-u-ks-primary',
PROVIDER = icu,
DETERMINISTIC = False
);
This works on PG14, but on PG15 it errors with 'parame
> > > That seems very reasonable; if the situation is similar on PostgreSQL,
> > > then I'd suggest making that very clear in the INSERT[2] and
UPDATE[3] docs.
> >
> > There is clearly no mention of such a guarantee in our documentation.
>
> Yes, which is just how SQL works: a set doesn't have any
Hi all,
I've seen various discussions around whether PG makes any guarantees on the
ordering of rows returned by the RETURNING clause (e.g. [1]). In a
nutshell, when executing a statement such as the following:
CREATE TABLE foo (id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY, data
INT);
INSERT I
> As I said before, your position seems reasonable. I've also got a couple
of reasonable complaints about IF EXISTS out there. But there is little
interest in changing the status quo with regards to the promises that IF
EXISTS makes. And even with my less constrained views I find that doing
anyth
>> Now, before creating tables, the ORM generates CREATE SCHEMA IF NOT
EXISTS, to ensure that the schema exists before CREATE TABLE; that's
reasonable general-purpose behavior.
>
> If the user hasn’t specified they want the schema created it’s arguable
that executing create schema anyway is reasona
> I would say it is reasonable in theory. But I cannot think of an actual
scenario that would benefit from such a change. Your stated use case is
rejected since you explicitly do not want tenants to be able to create
schemas - so the simple act of issuing "CREATE SCHEMA" is disallowed.
> [...]
>
Hi all,
I've received numerous complaints about CREATE SCHEMA IF NOT EXISTS failing
when the user lacks CREATE privileges on the database - even if the schema
already exists. A typical scenario would be a multi-tenant
schema-per-tenant setup, where the schema and tenant user are created
beforehand
> Yeah, it's not clear that forbidding this would make anyone's life any
> better. If you want an index on the UTC equivalent of a local time,
> you're going to have to find a way to cope with potential mapping
> changes. But refusing to let you use a generated column doesn't
> seem to help that.
> > It seems that PostgreSQL 14 allows using the AT TIME ZONE operator
within
> > generated column definitions; according to the docs, that means the
> > operator is considered immutable. However, unless I'm mistaken, the
result
> > of AT TIME ZONE depends on the time zone database, which is extern
Greetings hackers.
It seems that PostgreSQL 14 allows using the AT TIME ZONE operator within
generated column definitions; according to the docs, that means the
operator is considered immutable. However, unless I'm mistaken, the result
of AT TIME ZONE depends on the time zone database, which is ex
Greetings hackers,
I'm seeing some odd behavior around string prefix searching -
hopefully I've missed something here (thanks to Nino Floris for
originally flagging this).
In PostgreSQL 11, a starts_with function and a ^@ operators were added
for string prefix checking, as an alternative to LIKE
Hi all.
Back in 2016 I started a thread about making cancellations safer[1], I'd
like to try to pick this up again. Here a summary of the previous
conversation:
The main ask here is to allow clients to specify which command to cancel,
to avoid various race conditions where the wrong command is ac
Very interesting conversation, thanks for including me Dave. Here are some
thoughts from the Npgsql perspective,
Re the binary vs. text discussion... A long time ago, Npgsql became a
"binary-only" driver, meaning that it never sends or receives values in
text encoding, and practically always uses
Apologies for not responding earlier, busy times.
Fourth, it is not clear how many applications would break if COMMIT
>> started issuing an error rather than return success a with ROLLBACK tag.
>> Certainly SQL scripts would be fine. They would have one additional
>> error in the script output,
Greetings hackers.
While working on first-class support for PG collations in the Entity
Framework Core ORM, I've come across an interesting problem: it doesn't
seem to be possible to create a database with a collation that isn't
predefined, and there doesn't seem to be a way to add to that list. I
nd Ruby (in addition to JDBC and .NET),
commit APIs generally don't return anything - this is just how the API
abstractions are, probably because across databases nothing like that is
needed (the expectation is for a non-throwing commit to imply that the
commit occurred).
Shay
On Mon, Feb
ose that do commit on failed
transactions today, and it could be argued that those are likely to be
broken today (since drivers today don't really expose the rollback in an
accessible/discoverable way).
Shay
On Mon, Feb 24, 2020 at 3:31 AM Robert Haas wrote:
> On Sun, Feb 23, 2020 at 11:
On Fri, 14 Feb 2020 at 14:37, Robert Haas wrote:
>
>> On Fri, Feb 14, 2020 at 2:08 PM Dave Cramer
>> wrote:
>> > Well now you are asking the driver to re-interpret the results in a
>> different way than the server which is not what we tend to do.
>> >
>> > The server throws an error we throw an e
Thanks for the quick turnaround!
Tom Lane schrieb am Mo., 28. Okt. 2019, 16:57:
> Robert Haas writes:
> > On Mon, Oct 28, 2019 at 11:02 AM Shay Rojansky wrote:
> >> Before PG12, select strpos('test', '') returns 1 (empty substring found
> at first
Greetings hackers,
Before PG12, select strpos('test', '') returns 1 (empty substring found at
first position of the string), whereas starting with PG12 it returns 0
(empty substring not found).
Is this behavior change intentional? If so, it doesn't seem to be
documented in the release notes...
F
Tom,
> I have in fact committed that patch. It won't do anything for your
> problem with respect to existing installations that may have picked
>"localtime", but it'll at least prevent new initdb runs from picking
> that.
Thanks! At least over time the problem will hopefully diminish.
> I'm not sure we're any closer to a meeting of the minds on whether
> consulting zone[1970].tab is a good thing to do, but we got an actual
> user complaint[1] about how "localtime" should not be a preferred
> spelling. So I want to go ahead and insert the discussed anti-preference
> against "loc
> Yeah, this is something that some tzdb packagers do --- they put a
> "localtime" file into /usr/share/zoneinfo that is a symlink or hard link
> to the active zone file, and then initdb tends to seize on that as being
> the shortest available spelling of the active zone.
I see, I wasn't aware tha
Greetings everyone.
In (certain) out-of-the-box PostgreSQL installations, the timezone GUC is
set to "localtime", which seems to mean to query the OS for the value.
Unless I'm mistaken, the issue with this is that it doesn't allow clients
inspecting the TimeZone GUC to actually know what timezone
> The latter is important for similar reasons. JDBC caches prepared
statements internally and if the user changes the search path without using
setSchema or uses a function to change it then internally it would be
necessary to invalidate the cache. Currently if this occurs these
statements fail.
W
>
> > Thanks for insisting - I ended up setting up the environment and running
> > the tests, and discovering that some test-related changes were missing.
> > Here's a 3rd version of the patch. Hope this is now in good shape, let me
> > know if you think anything else needs to be done.
>
> Lotta wo
>
> On Tue, Jan 15, 2019 at 10:17 AM Shay Rojansky wrote:
> > Unfortunately I'm extremely tight for time at the moment and don't have
> time to do the appropriate hot standby setup to test this... As the patch
> is pretty straightforward, and since I'm hoping you
m 8c816354e820bf3d0be69d55dbf0052b1d27feeb Mon Sep 17 00:00:00 2001
From: Shay Rojansky
Date: Tue, 15 Jan 2019 18:49:40 +0100
Subject: [PATCH] Allow unlisten when in hot standby:wq
---
doc/src/sgml/high-availability.sgml| 16 ++--
src/backend/tcop/utility.c
Hi all,
Here is a tiny patch removing PreventCommandDuringRecovery() for UNLISTEN.
See previous discussion in
https://www.postgresql.org/message-id/CADT4RqBweu7QKRYAYzeRW77b%2BMhJdUikNe45m%2BfL4GJSq_u2Fg%40mail.gmail.com
.
In a nutshell, this prevents an error being raised when UNLISTEN is issued
ggestions on this though.
Regarding older versions of PostgreSQL, I hoping this is small enough to be
backported to at least active branches. In any case, a workaround already
exists to tell Npgsql to not reset connection state at all when returning
to the pool. This is what I'm recommending t
Hi hackers.
The documentation for DISCARD ALL[1] state that it is equivalent to a
series of commands which includes UNLISTEN *. On the other hand, the docs
for hot standby mode[1], state that UNLISTEN * is unsupported while DISCARD
is (although the docs don't specify whether this includes DISCARD
Peter,
I think this is all coming from Microsoft. The JDBC driver API was
> modeled after the ODBC API, and the ODBC specification also contains the
> {call} escape. Microsoft SQL Server is also the only SQL implementation
> to handle this stored function/procedure stuff totally differently: The
> Well, no, actually I think it wouldn't. Multiple rowsets coming back
> from a single query is, to my mind anyway, forbidden in the extended query
> mode. Yeah, we could probably get away with it in simple query mode
> (PQexec), but it's very likely to break clients in extended mode, because
> t
Peter, Tom,
Would it be possible for you to review the following two questions? Some
assertions have been made in this thread about the new stored procedures
(support for dynamic and multiple resultsets) whose compatibility with the
current PostgreSQL protocol are unclear to me as a client driver
> Shay>Npgsql currently always sends a describe as part of statement
> execution (for server-prepared messages the describe is done only once, at
> preparation-time). Vladimir, are you doing things differently here?
>
> The same thing is for pgjdbc. It does use describe to identify result row
> for
gt; function or procedure. How would that happen?
>>
>
> I'm saying that the driver needs to rewrite {call x} as "CALL x()" and
> expect optional resultsets and optional output arguments. For functions
> invoked as procedures this would be a single resultset with zer
Hi hackers, I've encountered some odd behavior with the new stored
procedure feature, when using INOUT parameters, running PostgreSQL 11-beta2.
With the following procedure:
CREATE OR REPLACE PROCEDURE my_proc(INOUT results text)
LANGUAGE 'plpgsql'
AS $BODY$
BEGIN
select 'test' into results;
>
>
>> Thanks for the input. It's worth noting that the equality operator
>> currently works in the same way: citext = text comparison is (surprisingly
>> for me) case-sensitive.
>>
>> My expectation was that since citext is supposed to be a case-insensitive
>> *type*, all comparison operations inv
>
> > Do you think it would be appropriate to simply add an strpos(citext,
> text)
> > overload to the extension to make sure this behaves more as expected? If
> so
> > I can try to submit a patch at some point.
>
> To me, if there's both a citext and a text parameter, then it's simply
> unclear wh
Thanks for your answer Pavel.
This is expected - it is side effect of PostgreSQL implementation of
> function overloading and type conversions
>
> after installation citext, you will have more instances of function strpos
>
> strpos(citext, citext)
> strpos(text, text)
>
Do you think it would be
Hi hackers.
The following works well of course:
test=# select strpos('Aa'::citext, 'a');
strpos
1
However, if I pass a typed text parameter for the substring, I get
case-sensitive behavior instead:
test=# select strpos('Aa'::citext, 'a'::text);
strpos
2
This see
>
> I am an author of one of the proposal (autoprepare which is in commit fest
> now), but I think that sooner or later Postgres has to come to solution
> with shared DB caches/prepared plans.
> Please correct me if I am wrong, but it seems to me that most of all other
> top DBMSes having something
>
> > Well, the issue is that implementing this is a major piece of work. This
> > post doesn't offer either resources nor a simpler way to do so. There's
> > no huge debate about the benefit of having a global plan cache, so I'm
> > not that surprised there's not a huge debate about a post arguing
Hi all,
Was wondering if anyone has a reaction to my email below about statement
preparation, was it too long? :)
(and sorry for top-posting)
On Tue, Feb 6, 2018 at 9:27 PM, Shay Rojansky wrote:
> Hi all.
>
> Various versions of having PostgreSQL caching and/or autopreparing
> sta
Am a bit late to this thread, sorry if I'm slightly rehashing things. I'd
like to go back to the basic on this.
Unless I'm mistaken, at least in the Java and .NET world, clients are
almost always expected to have their own connection pooling, either
implemented inside the driver (ADO.NET model) or
Hi all.
Various versions of having PostgreSQL caching and/or autopreparing
statement plans have been discussed (
https://www.postgresql.org/message-id/op.t9ggb3wacigqcu%40apollo13.peufeu.com
,
https://www.postgresql.org/message-id/8e76d8fc-8b8c-14bd-d4d1-e9cf193a74f5%40postgrespro.ru),
without cle
54 matches
Mail list logo
