that should have gone out with
floppy disks.
You need some extremely fundamental re-examination of your
design decisions. At the moment I am content to say that
Postgres does not support this storage mechanism and we
do not intend to do so in the future.
regards, tom lane
apply to large
tables.
regards, tom lane
[1]
https://www.postgresql.org/docs/current/using-explain.html#USING-EXPLAIN-CAVEATS
storage.
(But I too would not use Postgres-over-NFS for any critical data.
Too many moving parts. It's tough enough to ensure crash safety
with local storage.)
regards, tom lane
ike that. There might be related
discussion in our archives, but finding it could be difficult.)
regards, tom lane
"Peter J. Holzer" writes:
> On 2025-07-14 10:07:20 -0400, Tom Lane wrote:
>> That is primarily for safety reasons: if for some reason the
>> filesystem gets dismounted, or hasn't come on-line yet during
>> a reboot, you do not want Postgres to be able to wr
ite permissions on the mount
point, which dictates making a separate data directory (with
different ownership/permissions) just below the mount.
Do not bypass that ownership/permissions check. It is there
for very good reasons.
regards, tom lane
ate before it fails.
It occurs to me to wonder though if we couldn't provide more
context in the error about what lock is being waited for.
regards, tom lane
essage-id/flat/680bdaf6-f7d1-4536-b580-05c2760c67c6%40deepbluecap.com
We'll have a fix in next month's quarterly releases. For the moment,
you could either roll back to 15.12 or cherry-pick the fix at
commit fc0fb77c5.
regards, tom lane
ly (mainly to capture
your role definitions) and then pg_dump each database into a separate
compressed file.
regards, tom lane
it a try; be sure to use one of the pg_dump modes
that compress the data.
regards, tom lane
to some less PG-unfriendly distro while
you are at it.
regards, tom lane
things, both during inserts and searches".
If you had some evidence about when and how the index became corrupt,
it'd be worth studying that, but it sounds like you don't.
regards, tom lane
iness of using
passwords rather than other ID technologies (SSL certificates,
Kerberos/GSS tickets, etc) is feeling pretty twentieth-century.
regards, tom lane
r a new backend process to figure
out which database it's supposed to connect to and then detect whether
the per-DB connection limit is exceeded. In the meantime, that
session does count against the global limit, so yeah this isn't
surprising if the connection arrival rate is high enough.
ssues in some
contexts.
regards, tom lane
ot;my_database_ro";
Well, you aren't going to be able to do that if you're not superuser.
You could undo that ALTER in the source database and re-make the dump,
or edit the dump script to remove this command, or not use
pg_restore's "-1" switch and just ignore this error.
regards, tom lane
or
handling in COPY.
As you say, there are some cases where it's hard to avoid
an exception, but I'm not sure that "better error message"
is a good justification for throwing one. Still, in the
end it's your own judgment to make.
regards, tom lane
tivity() takes a snapshot of the view's contents and then
holds onto that snapshot until end of transaction. You can get
around that with pg_stat_clear_snapshot().
regards, tom lane
ty
hazards already. We'd do better to remove them, not add more.
So in short, while it would not be terribly hard to put in what
you suggest, we'd be creating a lot of work for people other than
ourselves. And the end result when all the dust had settled would
likely be just marginal security gains for a small subset of users.
regards, tom lane
Arnold Hendriks writes:
> Is there a way to ensure new IDs are in the usual range when starting
> in in single user mode?
No. Start the DB in normal mode, then create objects that you
want to be considered user-defined.
regards, tom lane
, we'd much rather
find it and fix it before 18.0 comes out.
regards, tom lane
an option in the client execute functions (and/or implement
> the backend support) to specify the expected number of statements.
I don't see the need for this given #2.
regards, tom lane
R triggers run as the instigating user.
regards, tom lane
te field). I didn't try this approach though.
regards, tom lane
list of
all of t's columns, just as would happen in a SELECT's output list.
Try
returning merge_action(), t
It might also be necessary to declare the target variable
"m_new_data" as being of type my_table rather than generic
"record"; not sure about that.
regards, tom lane
"lists." is
preferred nowadays, but there is no functional difference.
regards, tom lane
ndroppable
built-in types, we don't bother to make dependencies on them. And
you're not changing the domain's schema or owner, so its other
dependencies don't need to change.
regards, tom lane
Alvaro Herrera writes:
> I'm going to fix the query in the release notes for all past branches
> now, to avoid confusing people upgrading in the future ... hopefully not
> many, but I don't think it's going to be zero people.
OK, thanks.
regards, tom lane
ugh:
https://www.postgresql.org/docs/current/regress-coverage.html
regards, tom lane
Markus Demleitner writes:
> On Tue, May 13, 2025 at 10:12:46AM -0400, Tom Lane wrote:
>> Oh, well, that's your problem. The source tables' column types
>> need to match. Otherwise the UNIONs don't get flattened and you
>> don't get indexscans.
> Ahhh
that's your problem. The source tables' column types
need to match. Otherwise the UNIONs don't get flattened and you
don't get indexscans.
regards, tom lane
eans.
Sorry --- it means BRIN indexes that use one of the "xxx_bloom_ops" opclasses.
https://www.postgresql.org/docs/current/brin.html#BRIN-BUILTIN-OPCLASSES
regards, tom lane
rticularly short on coverage here:
one or the other code path is fully exercised in each case,
according to the coverage.postgresql.org results.
regards, tom lane
?
If you think there are changes we need to make, you'd better get
specific sooner not later. I'm not aware of any large fixes that
are pending, cf
https://wiki.postgresql.org/wiki/PostgreSQL_18_Open_Items
regards, tom lane
ml#350
regards, tom lane
;s an additional optimization blocker. I'd look
closely at whether the output column types of the UNION arms match.
regards, tom lane
is complaining about
is that that fails to happen if there's a UNION ALL in the way.
Postgres is capable of doing that in other cases, so it's a fair
question.
regards, tom lane
lanner's ability
to see that the outer query's join operator is compatible with
the table's index.
regards, tom lane
Dominique Devienne writes:
> On Fri, Feb 21, 2025 at 3:44 PM Tom Lane wrote:
>> DROP OWNED also removes relevant permission entries (which can be
>> thought of as things owned by the role, if you hold your head at
>> the right angle). See its man page.
> Except when it
he get-go.
Nonetheless, it's there now and is a pretty similar precedent.
regards, tom lane
Tim Starling writes:
> On 28/4/25 20:54, Tom Lane wrote:
>> Even if I were on board with arbitrarily adopting one of the two
>> possible interpretations, it's far from obvious to me that most people
>> would agree that "v" should mean the value from the existi
27;t understand why you don't have two slots, one for each).
regards, tom lane
ty exists around that software.
regards, tom lane
PG, thus allowing it to DWIM.
Even if I were on board with arbitrarily adopting one of the two
possible interpretations, it's far from obvious to me that most people
would agree that "v" should mean the value from the existing row,
rather than the new value. Better to make them say which they want.
regards, tom lane
for that.
We don't seem to have bothered with that exact syntax, but you
could spell it like
SET LOCAL lock_timeout TO DEFAULT;
regards, tom lane
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
1 - 100 of 2128 matches
Mail list logo
