but this looks like the value was just inline in the query.
regards, tom lane
Then the per-table data parts are written out, tracking where
each one begins. At the end, if the output file is seekable,
pg_dump seeks back to the start and re-writes the whole TOC
section, now with data offsets populated. But output to a
pipe won't be seekable.
ght be well advised to manually examine the data you are
stuffing into borgbackup. Right now we seem to be operating on
hypotheses, not facts, about what that looks like and how it's
different between your old and new server.
regards, tom lane
Ron Johnson writes:
> On Wed, Aug 27, 2025 at 10:16 AM Tom Lane wrote:
>> Don't use --format=custom (and not -v either). That causes pg_dump to
>> include the OIDs and pg_dump object IDs of all the tables and other
>> objects,
> That's interesting. Why? (Si
Ron Johnson writes:
> On Tue, Aug 26, 2025 at 6:08 PM Tom Lane wrote:
>> I'd expect pg_dump/pg_restore to preserve the physical row ordering,
>> simply because it doesn't do anything that would change that.
> But the rolling checksums are against a pg_dump file, not
vor to
replicate. Or, given that he said something about blocks, maybe
he's actually sensitive to where the free space is.
regards, tom lane
table would
look quite different to it.
regards, tom lane
Ron Johnson writes:
> Like I said, it's not completely linear. The real question, though, is
> whether PG looks for gaps in oid allocation once it wants to try and
> allocate an oid of uint32 max.
OID counter wraparound is not particularly a problem.
regards, tom lane
hubert depesz lubaczewski writes:
> On Fri, Aug 22, 2025 at 11:21:22AM -0400, Tom Lane wrote:
>> Interesting. That futex call is presumably caused by interaction
>> with some other process within the standby server, and the only
>> plausible candidate really is the star
rocess (which is replaying
WAL received from the primary). There are cases where WAL replay
will take locks that can block queries on the standby. Can you
correlate the delays on the standby server with any DDL events
occurring on the primary?
regards, tom lane
be bad enough to
justify jumping through hoops. But beta releases have always been
"use at your own risk".
regards, tom lane
27;s where we are.
regards, tom lane
Alban Hertroys writes:
> Or is it just my end that turns Scott's (and only Scott's) messages into
> gibberish Chinese?
What it looks like from here is that the message body is in UTF16:
every other byte is zero. That gives my email client indigestion,
too.
able scans faster.
Turning off autovacuum is an anti-pattern.
(Running a PG version that's four years past EOL is also an
anti-pattern, but you knew that. Should I ask whether it's
at least the final 9.6 minor release?)
regards, tom lane
ore than one row from each of a smaller set of pages would give a
nonrandom (because clumped) sample.
regards, tom lane
esql RPM
package telling you what's what.
regards, tom lane
wn".
No, the issue is that the procedure's named parameter is not named
"grantee_role" but something else. We'd have coerced the unknown
parameter just fine, except that we never considered this procedure
as a valid match at all.
regards, tom lane
e extended. We ended up not, but it was a judgment call.)
regards, tom lane
painless change that could be made in
that rat's nest, but I lack enough caffeine to see it.
regards, tom lane
d be hard to be definitive about
the argument name being the problem.
regards, tom lane
hese are things that
Postgres has done for decades and we'd surely break applications
if we changed them now.
regards, tom lane
have led to
the correct line of thought, so maybe giving 100% of the details
isn't necessary.
regards, tom lane
ml
> and pg_attribute:
> https://www.postgresql.org/docs/current/catalog-pg-attribute.html
> also have index information.
pg_index is the real source of truth here.
regards, tom lane
Laurenz Albe writes:
> On Fri, 2025-07-25 at 14:10 -0400, Tom Lane wrote:
>> So ... any chance you have a data type named _sa_setup_role?
> ... it could also be a type "sa_setup_role", and "_sa_setup_role"
> is interpreted as the corresponding array type:
O
1: select _sa_setup_role('af_repo_app');
^
DETAIL: Array value must start with "{" or dimension information.
So ... any chance you have a data type named _sa_setup_role?
regards, tom lane
e failed if
there were two, nor why we wouldn't see it in \df.
regards, tom lane
pg_cast. Also,
do you have any extensions loaded in that DB that aren't
in your other ones?
regards, tom lane
is resolving the ambiguity by
choosing that one.
"\df _sa_setup_role" would be illuminating.
regards, tom lane
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
1 - 100 of 2156 matches
Mail list logo
