ring. Which part of this is responsible for seeing that
that gets freed?
regards, tom lane
s why NoSQL
is not SQL, and this is one.
You can already approximate this sort of behavior in Postgres by
storing the less-structured aspects of your data in a JSON or XML
column. I'd suggest pursuing that approach rather than trying
to get us to mangle fundamental SQL semantics beyond recognition.
regards, tom lane
atching
version of pg_config would answer that.) Can you get a stack
trace from the crash?
https://wiki.postgresql.org/wiki/Generating_a_stack_trace_of_a_PostgreSQL_backend
regards, tom lane
promise that pg_dump version N will produce output
that you can use with pg_restore or server versions less than N,
whether they share the same archive version or not.
regards, tom lane
Igor Korot writes:
> On Wed, Apr 23, 2025 at 1:28 PM Tom Lane wrote:
>> If we do anything about this, I'd just say "systems that have
>> posix_fadvise()". If we write something more specific it's likely to
>> become obsolete, and it doesn't seem t
which
> would provide a bit more clarity.
If we do anything about this, I'd just say "systems that have
posix_fadvise()". If we write something more specific it's likely to
become obsolete, and it doesn't seem to me that it's hard for someone
to research "does my box have posix_fadvise()?".
regards, tom lane
rg/docs/current/datatype-numeric.html#DATATYPE-SERIAL
regards, tom lane
imeout: it is
the lock wait time after which we check to see if there's a deadlock.
If there's not, we just log the above message (if configured to do so)
and keep waiting.
If you want to fail after X amount of time, lock_timeout or perhaps
statement_timeout is what to set for that.
regards, tom lane
they sound
like they might be things that would take it on themselves to fool
with your stats-collection settings. I'd check their code for
something close to
SetConfigOption("track_counts", ..., PGC_S_OVERRIDE);
regards, tom lane
paces around the
parameter symbol. It's really a bug that it didn't do so already,
since closely-adjacent cases like digits immediately after the
"?" would already have caused failures.
regards, tom lane
lication
needn't connect to a particular database because it does no catalog
accesses (and hence can't run SQL). All it's able to do is suck out
the WAL stream. Logical replication can do SQL --- but it has to
connect to a specific database.
regards, tom lane
DE to it.
What extensions do you have installed?
regards, tom lane
that as a good thing.
(You can, of course, speculate about some major rearchitecting
of the system catalogs that would make this situation different.
I doubt that's going to happen at this point, though. There's
too much stuff that's dependent on how things are now.)
regards, tom lane
about terminology. These three
databases are part of the ecosystem and clients generally expect them
to be there. But the server doesn't depend on them to function.
Does that make them "system" databases? All depends on what you
think that means.
regards, tom lane
tified by name not by OID.
You can drop template1, and the only thing that will be unhappy
is CREATE DATABASE, and if you make a new DB that is named
template1 then CREATE DATABASE will be happy again.
regards, tom lane
runcate both the referenced and
referencing tables in the same command. The state of the triggers
is not material to this, since TRUNCATE doesn't fire them anyway.
regards, tom lane
h. So the collation-changed warnings that
Laurenz mentions are a lot more trustworthy for ICU collations.
2. It's at least *possible* to use your own fixed-version ICU
library if you're desperate enough. I don't think that would work
too well for libc; you're stuck with what the platform provides.
regards, tom lane
=postgresql.git;a=commitdiff;h=78637a8be
regards, tom lane
which is actually more than the real difference in runtime. (I hasten
to add that I don't have a lot of faith in our function cost
estimates. But the planner is quite well aware that a non-inlined SQL
function is likely to be expensive.)
regards, tom lane
e but
not library presence (else it wouldn't have thought that
the library was there either, I guess). Kind of weird,
but I'm still learning about meson.
regards, tom lane
restorecon might help if so.
regards, tom lane
es. That kind of looks like it might cause the
check to fail if the libnuma library isn't there, which is something
we do need to check, but doing it this way seems like it'd produce
a pretty misleading failure message if numa.h exists but the library
doesn't.
regards, tom lane
/include/numa.h isn't
there; but meson seems to fail as-expected without that,
or silently fall back to libnuma=disabled if you don't try to
force it.
regards, tom lane
own habit when writing a SQL function that I wish to be
inlined is to leave off all those markings. They won't matter
if the function is successfully inlined, and they might get in
the way of that happening.
regards, tom lane
able to do
CREATE TABLE mytable ...;
CREATE UNIQUE INDEX myindex ON mytable USING myam (...);
ALTER TABLE mytable ADD PRIMARY KEY USING INDEX myindex;
It doesn't look like we yet allow direct "USING myam" in
PRIMARY KEY, but sooner or later we might get to that.
Karsten Hilbert writes:
> Am Tue, Mar 25, 2025 at 06:55:34PM -0400 schrieb Tom Lane:
>> Works fine if you don't mess with the view's security_invoker
>> status.
> I know but doing so was kind of the point.
[ shrug... ] It's not going to work. When the view
x27;t feel a need to change the docs yet.
regards, tom lane
"David G. Johnston" writes:
> On Thu, Mar 20, 2025 at 11:54 AM Tom Lane wrote:
>> I think it's a mistake to suppose that pg_type_d.h is the only
>> place where there's a risk of confusion. We should be thinking
>> about this more generally: genbki.pl
'm not sure what other parts would be useful
to label.
As for CASHOID and LSNOID, surely those have been deprecated long
enough that we could just remove them?
regards, tom lane
"David G. Johnston" writes:
> On Thu, Mar 20, 2025 at 2:56 PM Tom Lane wrote:
>> That's documented elsewhere, I believe. For the foo_d.h files,
>> I think it'd be sufficient to do something like 0001 attached.
> WFM. Thanks.
Thanks for looking at it.
&
ed to be scanned as a
consequence of noticing that the columns have significant correlation.
The shape of that penalty function would be mostly guesswork though,
I fear. (Even with a clear idea of what to do, making this happen
seems a little complex --- just a SMOP, but I'm not very sure how to
wire it up.)
regards, tom lane
ntially usable for this query, and they all have different cost
curves depending on how selective the col_a condition is. Even the
index on col_b alone could potentially be the best, because it'll be
smaller than the two-column indexes. So if the col_a condition is
very unselective then it
th columns are covered by "idx_col_b_a".
They may be covered, but sort order matters, and that index has the
wrong sort order to help with this query. Try
create index on test_table(col_b, col_a);
regards, tom lane
a/k/a UTC+2. To get it to mean Indian Standard Time a/k/a Asia/Calcutta
you need
set timezone_abbreviations TO 'India';
(or more likely, adjust that in your installation's postgresql.conf).
See https://www.postgresql.org/docs/current/datetime-config-files.html
regards, tom lane
n's plan cache, but implemented in about the stupidest
way possible. Jacking that up and rolling the plancache.c
infrastructure underneath has been on my to-do list for years.
regards, tom lane
ry shortly [1]. Maybe somebody
will be excited enough to consider changing the docs in the back
branches. But since it was like this for a couple of decades,
I bet not.
regards, tom lane
[1] https://www.postgresql.org/message-id/flat/8216639.NyiUUSuA9g%40aivenlaptop
e made to do something
in debug builds but be no-ops in production builds. If you think
there's actually a realistic chance of the case happening in a
production context, you should use a regular if-test-and-ereport
instead of an Assert.
regards, tom lane
l from v_partially_private;
> to work but selecting from the view fails.
Works fine if you don't mess with the view's security_invoker
status.
regards, tom lane
gsql's FOREACH SLICE syntax:
https://www.postgresql.org/docs/devel/plpgsql-control-structures.html#PLPGSQL-FOREACH-ARRAY
You could probably make a custom version of unnest that uses that
and then keep your query about the same.
regards, tom lane
ependencies and requires
close attention to correct shell quoting, so it's seldom
preferable IMO.
regards, tom lane
llowing the original INSERT (which was outside both subtransactions)
to complete.
regards, tom lane
Adrian Klaver writes:
> On 3/22/25 17:31, Igor Korot wrote:
>> Is there a way to determine the server version from such a script?
> show server_version_num;
psql already populates its SERVER_VERSION_NUM variable from that
for you.
regards, tom lane
So if that's a top-level transaction, you can roll
it back, but if it's a subtransaction you can't.
regards, tom lane
going to show you
only a small fraction of the possible plans. If you are unhappy
because you suspect the planner rejected what would really have
been the best plan, the odds are good this won't help you because
the plan you want to see didn't survive long enough to be shown.
regards, tom lane
ave autonomous transactions (not yet anyway),
and you can't fake them like that.
A way that does work, I believe, is to set up a second session with
dblink[1] and use that to issue the autonomous transaction. Ugly
and inefficient for sure, but if you've gotta have it...
regards, tom lane
ide regex, which is a facility pg_restore
doesn't have access to. Maybe it'd be good enough to implement
the "*" and "?" wildcards and stop there, but I'm not sure.
regards, tom lane
;s the "Backwards compatibility" comment that's
bothering you, a look at pg_type.h will show you that that's
only intended to apply to the CASHOID and LSNOID symbols.
Everything below that in pg_type_d.h is machine-generated.
regards, tom lane
t;, edit out what
you don't want using any method you like, then use the edited list with
"pg_restore -L".
While I'd be in favor of improving pg_restore to accept wild-card
patterns, I'm very hesitant to start inventing new kinds of selection
switches for it. The interactions between such switches would be a
mess.
regards, tom lane
e than before. That would be
surprising, but with zero details it's hard to debug.
regards, tom lane
ch to a generic
plan if there's not too much change and the generic plan's estimated
rowcounts are in the same range. Or in other words, rather than
believing a cost comparison, what we'd look for is whether we get
basically the same plan in generic mode as in custom mode. I'm
not sure how to mechanize that, though.
regards, tom lane
.
And the case can't really happen without a function referencing the
index's base table; for example, a query from another session can't
see the uncommitted index at all.
regards, tom lane
Igor Korot writes:
> On Tue, Mar 4, 2025 at 8:37 PM Tom Lane wrote:
>> ... but given that you didn't specify any data type, I think the
>> parser will fall back to assuming that $1 is the same type as
>> "abf_type", whatever that is. Passing data in bin
is. Passing data in binary is not at all
forgiving about getting the data type right.
regards, tom lane
Garfield Lewis writes:
> I would like to know if it is possible to get the CTID from within
> the OUTPUT and SEND functions of a CREATE TYPE?
No. A datatype output function has no reason to expect that the value
it's handed has ever been in a table at all.
r
difficulty of
identifying the sign of an interval.
regards, tom lane
diff --git a/src/backend/utils/adt/timestamp.c b/src/backend/utils/adt/timestamp.c
index 9682f9dbdca..202bbd1edcd 100644
--- a/src/backend/utils/adt/timestamp.c
+++ b/src/backend/utils/adt/timestamp.c
@@ -6622,6 +662
might get different results in different months.
Another idea is to check, after doing each addition, to make
sure that the timestamp actually advanced in the expected
direction. But should we error out if not, or just stop?
regards, tom lane
happen here, because a subtransaction per
row is not practical.
regards, tom lane
ich that would.
Even ignoring the performance angle, this request seems remarkably
ill-specified. What is a "row-level handler" for errors that have
to do with identifying row boundaries?
regards, tom lane
'$PASSWORD'"';'
Note this will fall over with potential for SQL injection if there's a
single quote in the password, so better not use it with untrusted
input. On the whole I'd say "find some other way to do that".
regards, tom lane
te_subscripts($1, 1) i
$function$
regression=# create or replace function wrapper(anyarray)
RETURNS SETOF record LANGUAGE sql as
$$ select 1; select array_to_set($1); $$;
CREATE FUNCTION
regression=# select wrapper(array[44,55,66]);
wrapper
-
(1,44)
(2,55)
(3,66)
(3 rows)
regards, tom lane
ly
> certain lists (like hackers)?
It should work for any message that appears in our mail archives.
It's just a shortcut for
https://www.postgresql.org/message-id/MESSAGE_ID
regards, tom lane
g distinct grants (not same
grantor/grantee/granted roles). Wouldn't hurt to test.
regards, tom lane
ED then DROP OWNED is the recommended path.
regards, tom lane
Dominique Devienne writes:
> On Fri, Feb 21, 2025 at 3:33 PM Tom Lane wrote:
>> REASSIGN OWNED then DROP OWNED is the recommended path.
> Hi. Am I missing something? foobar does not OWN anything in this case.
> So I don't see how these recommendations are relevant to th
"David G. Johnston" writes:
> On Thu, Feb 20, 2025 at 9:05 AM Tom Lane wrote:
>> It used to be that if a superuser issued GRANT/REVOKE, the operation
>> was silently done as the owner of the affected object.
> That is still the case according to the docs (REVOKE)
ently,
> in two long running transactions? How am I supposed to resolve this?
The window is probably too small to hit if each restore is committing
as it goes, but if you run in --single-transaction mode then this
isn't surprising. I'd say don't try to run concurrent restores.
regards, tom lane
have to say "GRANTED BY "
to get that effect. I'm not entirely sure, but I think this is closer
to what the SQL standard says.
regards, tom lane
Ron Johnson writes:
> On Mon, Feb 17, 2025 at 4:36 PM Tom Lane wrote:
>> It's not pulling in the TOAST storage where the bytea column lives.
>> (pg_prewarm wouldn't have either, without special pushups.)
> Puzzling, since I ran "PERFORM *". What if I exp
es.
(pg_prewarm wouldn't have either, without special pushups.)
regards, tom lane
"David G. Johnston" writes:
> On Monday, February 17, 2025, Tom Lane wrote:
>> You'd really have to take that up with the author of pg_prewarm.
> This is our contrib module so this seems like the expected place to ask
> such a question. It’s neither a bug nor a to
s of their own, which is why
access rights are a poor gating mechanism for something that
needs to be applicable to indexes. Ownership could work,
because we make indexes inherit their table's ownership.
regards, tom lane
vocation of another set-returning function, it's just
going to take whatever that function returns first.
regards, tom lane
think is
better: an example with only half a dozen rows is going to be
swamped by startup costs.
regards, tom lane
ng to that, but I've not checked
into it.
regards, tom lane
e usual Emacs combinations Ctrl-A, Ctrl-E, Escape-B, Escape-F
and so on work in psql. I don't believe I did anything special
to configure that. Check your local readline documentation.
As Adrian notes, there's also \e, which is good for entering
or editing multi-line queries.
regards, tom lane
=?UTF-8?B?Sm9zZWYgxaBpbcOhbmVr?= writes:
> is there any plan for another minor release?
https://www.postgresql.org/developer/roadmap/
regards, tom lane
er versions
accepted them. But in a SET command I think no version will take it.
regards, tom lane
ed is that *passwordcheck is not an extension*.
It has no SQL-visible objects. It's just a shared library that
you must preload into the server, as per its documentation.
regards, tom lane
pretty clear: you need single quotes around the
value '1min', and you didn't provide them.
regards, tom lane
k that's missing?
The most obvious bet is that you installed a postgresql-contrib
package that is not really compatible with the server package
you're using and it put all the extension files somewhere else
than where the server expects to find them. Try checking the
file listing for the postgresql-contrib package you used.
regards, tom lane
done
locally on the old machine. You've got to shove the data over the
network sooner or later, so why not sooner?
regards, tom lane
ven on
> a system that old.
Another workaround is to connect from a remote instance of psql.
You'd probably need to modify the server's pg_hba.conf and perhaps
poke a hole in its firewall, but that's still easier than installing
new software.
regards, tom lane
)
Filter: ((id)::text = 'foo'::text)
(2 rows)
Your index supports bpchar comparison semantics, not text,
so it doesn't work for this query.
You could work around this by creating an index on id::text,
but TBH I'd question the choice to use a bpchar column in
the first place. It&#x
in spi.c.
regards, tom lane
resql.org/docs/current/libpq-ssl.html
Those cause some additional checks to be made, but it's not like
you can expect a completely broken certificate to work without them.
regards, tom lane
we did support that, it'd enormously complicate all timestamp
arithmetic --- and we could hardly do calculations with times in
the future at all, given the uncertainty around when leap seconds
will be declared. So if you want to do astronomical timekeeping,
you should use some other data type than timestamptz.
regards, tom lane
(
sfunc = hs_concat,
stype = hstore
);
Either way though, the order of aggregation is unspecified and thus
it's unclear which input will win when there are duplicate keys.
If that matters then you'll need to think about how you want
to resolve it.
regards, tom lane
that database
already. It didn't come from pgvector, so where did it come from?
regards, tom lane
to master, then it will start to use
whatever settings it has (and no, postgresql.conf is not copied
by the replication infrastructure).
regards, tom lane
tches what you use for the database.) If the results are different
then you definitely need to reindex; if they are the same then maybe
you're okay. Pay particular attention to columns containing
punctuation or non-ASCII characters, as those are the areas most
likely to see changes.
les at OS level are 65000.
I'm suspecting that you either need to increase that, decrease
max_files_per_process (which defaults to 1000), or decrease
max_connections.
regards, tom lane
y moving
in the direction of never letting the server see cleartext passwords.
It's already possible to configure libpq to refuse such requests
(see require_auth parameter), although that hasn't been made the
default.
regards, tom lane
ff and on for the last few days, and I've
found three different things that will need to be changed to make
it work again. At least one of them looks too invasive to consider
for back-patch. So don't hold your breath for a proper fix, but
perhaps you could use UNION as a workaround?
regards, tom lane
te the quotes) would provide an
intermediate level of compactness versus pain.
regards, tom lane
letion.
So in principle, you might get best results by defining your query
with DECLARE CURSOR and then using PQsetSingleRowMode on the FETCH.
But it'd really depend on the particular query whether this gives
any benefit.
regards, tom lane
nt results, and there have been few complaints about this
omission so far.
In the meantime, the most recommendable answer for you is probably
to switch over to using SERIALIZABLE mode. That'd require adding
application logic to retry after a serialization failure, but it
would produce co
le if you don't want to
think hard about this stuff.
regards, tom lane
wer than the extension's SQL files
(.../share/extension/repmgr.control etc).
You need to straighten out that version mismatch.
regards, tom lane
nd d0 is not a utf-8 character so it block reading whole log file by
> file_fdw.
You certain that server is 16.6? Because we fixed that in 16.5:
https://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=4fd4d7653
regards, tom lane
1 - 100 of 2083 matches
Mail list logo
