itialized with the new version's initdb. pg_upgrade then
transfers data into that from the old database (-d argument).
regards, tom lane
p...@pfortin.com writes:
> On Sun, 15 Jan 2023 14:47:35 -0500 Tom Lane wrote:
>> I think you misunderstand how this is supposed to work. The -D
>> argument should point at an *empty* data directory that has been
>> freshly initialized with the new version's initdb. pg
un there would
still be two copies of the DB on the new disk, which doesn't seem
like what he wants.
regards, tom lane
refresh your memory on what that was.
regards, tom lane
Harmen writes:
> On Sat, Jan 14, 2023 at 11:23:07AM -0500, Tom Lane wrote:
>> If you are running a reasonably recent PG version you should be able to
>> fix that by setting up "extended statistics" on that pair of columns:
> CREATE STATISTICS dist4 (ndisti
IN notation is gone.)
> I'm trying to understand the precedence of AND/OR operations when
> everything is not tied together with ()'s.
The OR is lower priority than all the ANDs, so yeah moving some
clauses to be after the OR would change the semantics. I think
you probably need some more parentheses here; it's not clear
exactly what semantics you are after.
regards, tom lane
ing kept around could be different.
regards, tom lane
es to you that way.
If that tool is pg_dump, this is its normal behavior. There will
be other commands in its output that build the rest of the
partitioned index set.
regards, tom lane
ING
btree (f1)
foo_2 | foo_2_pkey | CREATE UNIQUE INDEX foo_2_pkey ON public.foo_2 USING
btree (f1)
(3 rows)
If you wanted to reconstruct this from individual parts, as pg_dump does,
you'd issue those commands and then connect them together with ATTACH
PARTITION commands.
regards, tom lane
would be just as broken
if we restricted this.
regards, tom lane
with dashes (36 bytes + length overhead), the gap would
> narrow.
Yeah, especially if your database is not using C locale. The
strcoll or ICU-based comparisons done on string types can be
enormously more expensive than the memcmp() used for binary
types like native uuid.
regards, tom lane
the next desert of dead tuples.
If turning synchronize_seqscans off changes the behavior, that'd
be a good clue that this is the right theory. As for a real fix,
it might be time for a VACUUM FULL or CLUSTER on that table.
regards, tom lane
Dimitrios Apostolou writes:
> On Tue, 31 Jan 2023, Tom Lane wrote:
>> Do you get the same 10 rows when you repeat the command?
> Yes. Just tested with both cold and hot caches. The first 10 rows are
> exactly the same, either they return slowly or immediately.
Hm. I don'
wo pointers-to-numerics, not the values of
the numerics. You'd need to invoke numeric_cmp() if you
want a sensible result.
regards, tom lane
rong thing.
(You might be thinking of the free-space map, but we don't maintain
that rigorously enough to trust it as a guide to skipping pages.)
regards, tom lane
u get some number of zero-column rows. psql's display of that is
a bit idiosyncratic, but nobody's bothered to improve it.
regards, tom lane
Especially if you are using
version-mismatched psql and libpq, which seems moderately likely.
regards, tom lane
g this all to a constant if so, whereas older
ones might not have. If you care about when/whether crypto_secretbox_open
runs, then it must have side-effects, so it shouldn't be IMMUTABLE.
regards, tom lane
is
likely to look odd. How much do you care about that?
regards, tom lane
ur shoulders and stick with C collation. It's likely
to be faster than any alternative.
regards, tom lane
ion/lctype?
No, at least not in Postgres.
regards, tom lane
he dropped
>> column replaced by a null value."
> VACUUM FULL doesn't rewrite the table?
It moves all the tuples into a new file, but it does not rebuild
individual tuples, as would be needed to delete column values.
regards, tom lane
rid of the 4GB limit on stat() results in v14,
or so we thought.
Anyway, as far as I can find pg_dump can only reach that error message
via fsync_dir_recurse(), which suggests that a workaround might be
to add --no-sync so it doesn't try to fsync its output.
regards, tom lane
e a net loss in some use-cases. We do manage
to get past that --- the position() code didn't have BMH to start
with --- but it definitely requires solid evidence.
regards, tom lane
d without hacking C code.
regards, tom lane
d with --trace-children=yes.
For leak tracking you probably also want
--leak-check=full --track-origins=yes --read-var-info=yes
regards, tom lane
free buf if you relocate it larger?
Usually it's more like "you need to pfree if you allocated in a long-lived
memory context". elog is working in ErrorContext which it expects will
be reset when the dust settles.
regards, tom lane
Jeffrey Walton writes:
> On Fri, Feb 10, 2023 at 10:04 AM Tom Lane wrote:
>> You have to valgrind the whole cluster AFAIK. Basically, start
>> the postmaster under valgrind with --trace-children=yes.
>> For leak tracking you probably also want
>> --leak-check=full --t
YZE has never yet examined the table contents, but it's
also the state when we have found the table to be empty. Because
of the former case, older planner versions don't like to believe
that a table is empty even if reltuples says so.
regards, tom lane
ble.
What do you mean by "visible" ... that "select * from pg_database"
shows only "postgres"? If so, the most likely theory is that they
are not connecting to the same Postgres instance you are.
There's not any permission-based filtering on what you can see in
that catalog.
regards, tom lane
tions, and maybe
log_statements too, and then looking into the postmaster log to see
what happens when they try to connect.
regards, tom lane
. external IP address to consider, not to mention IPv4 vs
IPv6)
* Is pg_hba.conf set up to allow the connection?
Only if the problem is at that last step will log_connections help much;
otherwise, no data is reaching Postgres at all.
regards, tom lane
on expensive stuff
that we're willing to do to clean up after brain-dead ORMs, because
the costs of that will also be paid by not-so-brain-dead applications.
In the example at hand, it's hard to argue that the query generator
sending this query shouldn't know better, since as Peter points out
the IS NULL check is redundant on its face, primary key or not.
regards, tom lane
omparing your two test queries on released
branches, I see maybe 2x penalty for the ORDER BY NULL, not 30x.
(In HEAD there's only about 13% penalty.) I wonder what PG version
you are testing.
regards, tom lane
id of the useless targetlist column altogether would
be way more invasive, and I'm not inclined to try.
regards, tom lane
David Rowley writes:
> On Mon, 20 Feb 2023 at 13:17, Tom Lane wrote:
>> We could teach createplan.c to generate a WindowAgg plan node
>> that omits the useless column from ordNumCols/ordColIdx/etc, but I'm not
>> sure if that'd save much in itself.
> I wonder
e[1] myname DB saved because it was open?
Yup.
> If the dump file
> contains the above statements, how can I be absolutely certain I won't
> lose the DB?
Reading the manual is advisable. --create --clean specifies exactly
that the target database is to be dropped and recreated.
regards, tom lane
first thing I'd think of though is that
it's blocked waiting for a lock, in which case looking into pg_locks
to see what is blocking it could help.
regards, tom lane
lters, and
query those views when you want restrictions?
> Alternatively, are the concerns about changed plans unfounded?
Hard to tell without experimentation.
regards, tom lane
han
"IS NOT DISTINCT FROM" for what is surely a pretty common
requirement. But we probably need to get the SQL committee
on board with any replacement syntax for that.
regards, tom lane
d in JSON can be
extracted into a valid string of our text datatype.
Storing data in json and then casting to jsonb on-the-fly seems like
about the worst possible combination of choices.
regards, tom lane
[1] https://www.postgresql.org/docs/current/datatype-json.html
-linked
between the old and new clusters.
regards, tom lane
Adrian Klaver writes:
> On 2/27/23 08:36, Tom Lane wrote:
>> If it was based on something like "du", perhaps the measurement
>> was fooled by the fact that most of the data files will be hard-linked
>> between the old and new clusters.
> Does that happen witho
the
later query referencing ROSTER_TABLE fails because it's parsed before
the CREATE TABLE executes. (Improving that has been on the to-do list
for a couple of decades, so don't hold your breath...) I suggest putting
the whole thing, not just part of it, into plpgsql.
regards, tom lane
ving the distinct-ification into an earlier
processing step (i.e. a sub-select), or maybe thinking harder
about why you're getting duplicates in the first place.
regards, tom lane
Thorsten Glaser writes:
> On Mon, 27 Feb 2023, Tom Lane wrote:
>> Well, yeah. Simplify it to
>> SELECT array_agg(DISTINCT x ORDER BY y) FROM mytable;
> That’s… a bit too simple for this case.
Sure, I was just trying to explain the rule.
>> For the specific example you
iki.postgresql.org/wiki/Locale_data_changes
although that focuses on the even-more-annoying case where locale
sort order changes between releases of a single OS.
regards, tom lane
sename",
> "application_name", "client_addr", "client_hostname", "client_port",
> "backend_start", "xact_start", "query_start", "state_change",
> "wait_event_type", "wait_event", "state", "backend_xid", "backend_xmin",
> "query", "backend_type");".
You're really expecting us to intuit a lot from this amount of
detail, aren't you? But okay, I'll guess: I think this is a
view on pg_stat_activity, and it's not accounting for the fact
that pg_stat_activity gained some columns between v12 and v14.
Probably your best bet is to drop that view, do the upgrade,
and recreate the view with adjustments.
regards, tom lane
x27;ll find out the syntax for converting
the value of a psql variable to a SQL string literal:
regression=# \set foo BAR
regression=# select ':foo';
?column?
--
:foo
(1 row)
regression=# select :'foo';
?column?
--
BAR
(1 row)
What the server got in the last case was "select 'BAR';".
regards, tom lane
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. DDL commands in Postgres are
transactional unless their man page explicitly says they're not.
r
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 point to where in the doc this is stated?
For example, for CREATE DATABASE the firs
an't
be implemented with existing features such as FILTER and ORDER BY.
regards, tom lane
EN state. Unfortunately,
netstat is not much help at locating the processes listening to such
ports. This might be more help:
lsof -n | grep 5432
regards, tom lane
get into that sort of problem without anywhere near the amount
of complexity embodied in this example --- for instance, I'm pretty
sure we don't re-parse type references just because somebody else
executed an ALTER TYPE RENAME somewhere.
regards, tom lane
rget, don't use SELECT INTO;
use an assignment statement.
regards, tom lane
d a project convention that I/O functions should be at
worst STABLE, allowing calling functions to be STABLE as well.
regards, tom lane
supply the exact
error messages. But ... do you have openldap-devel installed, or just
the base openldap package?
> The compile step and make world steps work perfectly if the script is
> run under root.
That is odd. Permissions problems on the libraries, maybe?
regards, tom lane
ut of OIDs?
The counter wraps around. But it does so at 2^32 not 2^31.
> Are they Cluster-wide unique or it depends on the OID type?
They're unique per catalog. We don't attempt to guarantee
more than that. In practice, they'll be unique across the
installation until after the first OID wraparound, and then
not so much.
regards, tom lane
k,
As Christoph said, you're responsible for supplying a BEGIN and COMMIT
around the call.
regards, tom lane
of Postgres that
supports ICU and you can use an ICU locale. That code path doesn't
appear to have any hard-wired assumption about how many characters
in convert to how many out.
regards, tom lane
dn't measure much difference between having it on vs. off in a
pgbench test with all the clients counting the same table ... but maybe
on other hardware the effect would show up.
regards, tom lane
with seqscans disabled?
regards, tom lane
ge.
Or modify pg_hba.conf to let you in without a password. Local
peer auth, for example, is perfectly secure.
regards, tom lane
d be that increasing the table's statistics target and
re-ANALYZEing would help.
regards, tom lane
rolname !~ '^pg_';
on the new cluster?
regards, tom lane
l just report the time spent in the FK triggers,
not what they were doing exactly.
IIRC, you can see the CASCADE operations with contrib/auto_explain,
if you enable auto_explain.log_nested_statements.
regards, tom lane
ing code for arrays?
> I didn't find the usual _send() and _recv() functions (but looking on
> github online...)
https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/utils/adt/arrayfuncs.c;h=87c987fb2704761c59333bf8c1fee47e5c14c598;hb=HEAD#l1583
regards, tom lane
ou
probably need to bite the bullet and maintain a btree index.
regards, tom lane
s) not the array structure proper.
regards, tom lane
rations.)
The usual compromise is to use just one config --- either 'simple',
or a specific language if you have a preponderance of data in that
language --- and tolerate the fact that searches won't be too bright
about things like reducing stemmed words.
regards, tom lane
ter
child process) crashed. I lack an explanation of why your log
trace doesn't show anything about that, but that seems like an
idea to pursue. What is the logging mechanism you're using?
regards, tom lane
not the same thing.
Neither one guarantees that Y is globally unique. We have no mechanism
for enforcing uniqueness across partitions except for partition key
columns.
regards, tom lane
ented at the user level.
Want to propose some doc text?
regards, tom lane
body before reporting odd slowness of trivial
commands like BEGIN. I failed to find the thread(s) in the archives
though, so I'm not sure if we identified the cause.
regards, tom lane
hat you're evidently incurring the wrath of the OOM
killer, you should try to understand why the kernel thinks it's
under memory pressure. Do you have too many processes, or perhaps
you've configured too much shared memory?
regards, tom lane
see what to do with the
description of the @@ operator (and the equivalent jsonb_path_match
function). Some more examples for those might help.
regards, tom lane
[1]
https://www.postgresql.org/docs/devel/functions-json.html#FUNCTIONS-SQLJSON-PATH
54d5faa2f1cec03911
I think this was not fixed in full until 2008:
https://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=0171e72d4da2da7974ff13c63130e2175cebee88
Either way, though, whatever Steve is looking at is far past its
sell-by date.
regards, tom lane
tion protocol to support it.
regards, tom lane
Steve Rogerson writes:
> That's the one. I can't see which pg version(s) this turned up in.
The pg_time_t change was in 8.0, the later one to support 64-bit tzdata
was in 8.4.
regards, tom lane
Atul Kumar writes:
> Please help me in telling that how I can take the backup of one single
> schema without its data using pg_dump utility ?
Doesn't "pg_dump -s -n ..." do what you want?
regards, tom lane
WITH clauses can now be inlined
whereas the previous behavior was always the equivalent of WITH ... AS
MATERIALIZED. The new behavior is usually better but we have seen
cases where it loses. Your issue could be something else entirely
though.
regards, tom lane
OK and 15's less so) but that is not why we see "Workers
planned: 2, Workers launched: 0". Either provision enough parallel
workers to fix that, or disable parallelism.
regards, tom lane
thing to do is more like
INSERT INTO t(data) VALUES ('a'), ('b'), ('c') RETURNING data, id
and then explicitly match up the returned "data" values rather than
presuming they appear in the same order you wrote them in in VALUES.
Admittedly this might be problematic if some of the VALUES rows
are identical, but how much should you care?
regards, tom lane
deadlock_detectedthen does it mean PG has already rolled back the tx
> and therefore I should not attempt to roll it back again? Thanks,
No, treat these the same as any other error.
regards, tom lane
Daniel Gustafsson writes:
> Reviving an old thread that got buried, I propose to apply the attached and
> backpatch it as OpenSSL of such vintage is equally likely to be used in old
> versions of postgres as current.
+1
regards, tom lane
ions added by rules. This ensures
that the actions can see the to-be-updated or to-be-deleted rows;
otherwise, the actions might do nothing because they find no rows
matching their qualifications.
regards, tom lane
[1] https://www.postgresql.org/docs/current/rules-update.html
the generated columns twice in case a BEFORE trigger makes any relevant
changes in the row, and that seemed unduly costly. But anyway, it seems
like being consistent with that behavior is another reason not to change
the behavior of ON UPDATE rules.
regards, tom lane
> as a bug—and was fixed. When did the behavior change here?
Probably here:
https://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=c1b7a6c27
regards, tom lane
ough. See the more-or-less
SQL-standard WITH ORDINALITY option for functions-in-FROM. It seems to me
that it could be plausible to attach WITH ORDINALITY to a VALUES clause,
which would give you a rock-solid connection between the VALUES rows and
the ordinality-column values, and then you could include that column in
RETURNING.
regards, tom lane
_NUMBER.
We already rejected the idea that INSERT must preserve the order of
the incoming tuples. Please don't re-propose it with different
wording.
regards, tom lane
se others failed.
Withholding judgment about which one to cancel until something has
committed ensures that more than zero work gets completed.
Also note that AFAICS we do notice fairly promptly once a transaction
has been marked as doomed; it's not the case that we wait till the
transaction's own commit to check that.
regards, tom lane
_changelog >../pg-revision-history
This produces output that collates matching commits in different
branches.
regards, tom lane
13), so they cannot “see” one another's effects
on the target tables.
regards, tom lane
precisely match every possible mistake. As
Adrian said, brevity in the docs does have value.
regards, tom lane
rences to orafce functions.
regards, tom lane
doesn't show that, adding to your confusion. The commands
that don't work for you are failing because you assume the
partitions are in "my_schema", except in some places where
you leave that off, and then it does work because public
is in your search_path.
regards, tom lane
;
> LINE 1: alter table my_schema.my_table drop partition "my_schema"."m...
>^
I'm not sure why the error cursor is pointing there, but
"drop partition" is not a valid subcommand. Try "detach
partition".
regards, tom lane
erialized value. Note that certain previous
specifications of JSON constrained a JSON text to be an object or an
array.
However, there certainly are some operations that require the top-level
value to be an object or array.
regards, tom lane
ve 10 indexes, if only 1 index will be updated,
> why the rest 9 indexes couldn't use HOT update?
Whether it's a HOT update is a property of the table row, not of
individual indexes.
regards, tom lane
er is a constant.
Lot of unfinished work here :-(
regards, tom lane
1401 - 1500 of 2962 matches
Mail list logo
