hubert depesz lubaczewski writes:
> On Mon, Aug 30, 2021 at 10:11:22AM -0400, Tom Lane wrote:
>> I don't suppose you could send me a schema-only dump of that
>> database, off-list? I'm now quite curious.
> Asked the owners for their permission.
BTW, I think you c
Matt Zagrabelny writes:
> $ \C foo
> Title is "foo".
> How do I suppress the echo of "Title is..." from psql?
The -q switch would do it ...
regards, tom lane
ely? Is your
Postgres up-to-date? How about the underlying OS?
regards, tom lane
at identical corruption would happen to different installations.)
regards, tom lane
nt of it. If that's not a good characterization of
the goal, it'd help to tell us what the goal is. (Just saying "I
want to use jaccard similarity" sounds a lot like a man whose only
tool is a hammer, therefore his problem must be a nail, despite
evidence to the contrary.)
regards, tom lane
timize the extractions.
Otherwise, see
https://wiki.postgresql.org/wiki/Slow_Query_Questions
regards, tom lane
But at least it squares with the observation that
only that table is having issues.
BTW, are you *entirely* certain that your application never inserts
non-default values into that column?
regards, tom lane
uot;, which
requires that (a) no indexed column changes and (b) there is room
on the same page for the new copy of the row. Ensuring (b) requires
running with a fairly low fill-factor, which bloats your table and
thereby creates its own costs. Still, that might be worth doing
depending on your particular circumstances.
regards, tom lane
x27;t recall for sure, but I think you could have made this work
by putting the socket path (/Volumes/Free/Upgrade) instead of
"localhost" in the .pgpass file.
regards, tom lane
uld not be skipping
the cleanup. If it is, the threshold for that is too aggressive.
Assuming that that choice was made appropriately, I think the advice you
propose here will just cause people to waste lots of cycles on VACUUM
runs that have only marginal effects.
regards, tom lane
n advance of
knowing whether the query will throw an error.)
regards, tom lane
different from 1262, nor could this change
of attnums for the surviving rows ever have happened via Postgres-internal
processes.
I'm about ready to file this under "you broke it, you get to keep both
pieces".
regards, tom lane
ssed this earlier. (The EXPLAIN EXECUTE output itself
> does not contain an "Output:" line, so I didn't notice this lack of
> parameter substitution in psql.)
I think EXPLAIN VERBOSE would add that bit of detail.
regards, tom lane
eep expecting us to change it. On the other
hand, nobody's put forward any improvement proposals.)
regards, tom lane
e.
> This is Postgres's bug
Yeah, agreed. Looks reasonably simple to fix, though.
regards, tom lane
suspect this is the source. Is '/* DBD::Pg ping test v3.5.3 */' the last
> thing it did, but because it's idle now, it's actually doing nothing ?
Yes, exactly.
regards, tom lane
x27;t want. Single-user mode is pretty limited,
because what it really is is single-process mode: there will be no
background support processes such as bgwriter, autovacuum, replication
support, etc. The system runs, more or less, but performance is not
going to be great.
regards, tom lane
fined what
results you get. What's the point of doing it like this, rather than
just having cte1 return all the columns needed?
regards, tom lane
t (incorrectly ordered) indexes on textual columns.
If you don't mind reindexing all of those after the update, you
could proceed with this plan. Otherwise, pg_dump-and-restore might
be a safer idea.
regards, tom lane
oth would be a no-go, as I cannot INSERT a specific oid to add
> one back as far as I know.
> So how do I delete only one of the two?
Delete by ctid.
select ctid, oid, * from pg_largeobject_metadata where oid=665238;
delete from pg_largeobject_metadata where ctid = 'pick one';
regards, tom lane
7;d think that creation of such duplicates would still be
stopped by that unique index. There's something mighty odd here.
regards, tom lane
pretty heavyweight things. Even after the connection
is complete, there's overhead involved in populating caches and so
forth. You'd be well-served to use a connection pooler and/or try
to keep an application's connection open once made.
regards, tom lane
ey want
unlabeled timestamps to be interpreted in a particular zone.
regards, tom lane
eady covered upthread, the can of
worms that that opens is so large that nobody has wanted to try to
tackle it as a primitive Postgres datatype.
regards, tom lane
x27;d be the old PG server version upgraded from, the
new server version upgraded to, the versions of pg_upgrade and
pg_dump (these probably should match the new server version, but
I'm not certain we enforce that), and the pg_upgradecluster
script version?
regards, tom lane
orted to do 86m heap fetches along the
way to returning 812m rows, so the data is apparently pretty dirty.
It's possible that a preliminary VACUUM to get page-all-visible hint
bits set would be a net win.
regards, tom lane
Michael Lewis writes:
> On Wed, Sep 22, 2021 at 2:48 PM Tom Lane wrote:
>> The "index-only" scan is reported to do 86m heap fetches along the
>> way to returning 812m rows, so the data is apparently pretty dirty.
> Do you say that because you would expect many more
on't persist
such cursors before we get into the uninterruptible part of COMMIT.
regards, tom lane
problems even
with taking over the => digraph, never mind single characters.
In the end the functionality-versus-problems ratio is just not going
to be very good.
regards, tom lane
We fixed it in v13, but the changes seemed far too invasive
to risk a back-patch [1].
regards, tom lane
[1] https://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=1281a5c90
re are so many other code paths there. Not sure how
to avoid future bugs-of-omission.
regards, tom lane
n
was, so we can try to understand where the oversight is.
regards, tom lane
tachments as this one was. (For
reference, it's not showing up in the PG archives.)
You're generally a lot better off *not* using screenshots to make
your point.
regards, tom lane
acktrace from
the point of the error?
https://wiki.postgresql.org/wiki/Generating_a_stack_trace_of_a_PostgreSQL_backend
regards, tom lane
led as not being worth
the maintenance effort, so I doubt you'll find much interest
in resurrecting that one.
regards, tom lane
Igor Korot writes:
> On Sun, Oct 3, 2021 at 3:11 PM Tom Lane wrote:
>> Having said that, it does work on non-Windows machines to do
>> ./configure ...
>> cd src/interfaces/libpq
>> make -s install
> Well, asking people to download the whole source tree just to bui
package were to be picked up
and redistributed by Red Hat, or Debian, or any other open-source
vendor, the very first thing they'd do is strip it of any such copied
code. They don't want the management hassle of having to update
multiple copies of libpq or mysql-connector or other stuff.
regards, tom lane
table to the query after the additional
tables, not before them?
Not sure I'd call this a bug exactly, but maybe there's room for
improvement. Or maybe there is an actual semantic issue that
I'm not seeing right away.
regards, tom lane
Nikhil Benesch writes:
> On Mon, Oct 4, 2021 at 1:48 PM Tom Lane wrote:
>> My mental model of these things is that the target table is cross-joined
>> to the additional tables as though by a comma in FROM [...]
> Mine as well.
> I just managed to dredge up some history h
You could, but then you'd be creating a self-join on the target table
(and would need to add suitable WHERE clauses to constrain that join).
This might be the best near-term workaround, but it does seem ugly
and inefficient.
regards, tom lane
cept negative year values as
meaning BC. Before that, they threw errors.
regards, tom lane
le
complain about this topic every so often, but I've not yet seen a proposal
that would improve matters.
regards, tom lane
y reported and fixed at
https://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=e0eba586b
regards, tom lane
n;
show log_directory;
regards, tom lane
Hm. Not much there. You might try turning on log_connections to be
sure, but what this looks like is that the connection request never
got to the server. Maybe you've got some middleware involved, like
pgpool or some other connection pooler?
regards, tom lane
azy.
The default behavior is first to compress any large field value(s),
and then if the row is still too big, push them out-of-line in
compressed form. You can modify that strategy on a per-column
basis if need be, but I've seldom heard good reasons to.
regards, tom lane
"p...@cmicdo.com" writes:
> The 13.4 release notes document the following:
> > Fix mis-planning of repeated application of a projection step (Tom Lane)
> >
> > The planner could create an incorrect plan in cases where two
> > ProjectionPaths w
er and therefore
corrupt on the replica. See
https://wiki.postgresql.org/wiki/Locale_data_changes
regards, tom lane
rrent notify queue position exactly
matches the supposed global minimum position. This corresponds to
a known bug that was fixed in 10.16, so I'd suggest upgrading.
As a temporary workaround you could restart that server, but
likely the problem would recur after awhile.
regards, tom lane
time as other major
distros. The most probable explanation for the OP's problem seems
to be failure to update ca-certificates and/or openssl at all for
several years.
regards, tom lane
[1] https://letsencrypt.org/docs/certificate-compatibility/
lemented OpenSSL's "workaround 1" [2]
on RHEL7, but they left well enough alone on newer platforms.
They could not have pushed out the DST cert removal much before
that cert expired, for fear of causing unnecessary problems
elsewhere. So that's why the seemingly short notice.
gt; (start_1, common_endpoint) overlaps
> (start_2, common_endpoint)
> )::text
> from c;
> The result is "true". Seems to me that the doc is therefore wrong
Huh? Those intervals have lots of points in common, not only a
single point. The documentation is referring to a case like your
second example.
regards, tom lane
it seems clear to me in the context of the whole paragraph that
"endpoint" means either end of the range. "Boundary point" would be
longer but I doubt any clearer.
regards, tom lane
nstance that two
time periods with only an endpoint in common do not overlap.
regards, tom lane
[1] https://www.postgresql.org/docs/current/functions-datetime.html
options listed below, there is one
method-independent authentication option clientcert, which can be
specified in any hostssl record. When set to 1, this option requires
the client to present a valid (trusted) SSL certificate, in addition
to the other requirements of the authentication method.
regards, tom lane
ge if you don't use "-h localhost" but instead let it
default to a Unix socket? (I don't have any real reason to think
that it would change, but we're grasping at straws here.)
regards, tom lane
rly.
BTW, I think a more likely explanation is "one of the pg_dump or
pg_restore worker processes crashed". Why that should be is still
a mystery though.
regards, tom lane
e second range's.
(I gloss over the question of what to do with NULL endpoints; but the
apparent redundancies in the above seem to be meant to define what
happens with NULLs.)
I submit that our description using half-open ranges is clearer than
the spec's. Nonetheless, they're equivalent.
regards, tom lane
ability to set an ICU collation as a database's default.
But you can attach ICU collations to individual text columns,
and maybe that would be a good enough workaround.
regards, tom lane
[1] https://wiki.postgresql.org/wiki/Locale_data_changes
-in software to receive incoming
connections". I also have SSH enabled, though that doesn't
seem too relevant here.
regards, tom lane
period.
So I'm suspicious that this is an RDS-specific effect, and thus that
you should consult Amazon support first. If they say "no, it's Postgres
all the way down", then we need to look closer.
regards, tom lane
differs from the community version by quite a bit, but
> I'm unsure how much their regular Postgres offering differs, if at all.
Yeah, Aurora is definitely a different beast at the storage level.
I'm not entirely sure about RDS.
regards, tom lane
erator
is essential to an opclass when it is not really.
regards, tom lane
[1] https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=9f9682783
[2] https://www.postgresql.org/message-id/flat/4578.1565195302%40sss.pgh.pa.us
of short-lived temporary tables, maybe?
> Also what happens when pg_class.oid reaches 4B.
Nothing. The counter will wrap around and you won't notice a thing.
regards, tom lane
I wrote:
> Anyway, it doesn't look like there's much hope of improving this
> aspect without a significant rewrite.
Just to close out this thread: I've now posted such a rewrite at
https://www.postgresql.org/message-id/2273648.1634764485%40sss.pgh.pa.us
regards, tom lane
could do "SELECT CURRENT_USER" first.
I'm not sure if we want to change a security-relevant behavior
in released branches. But if we don't, we probably need to
change the docs to something like "(by default, the logged-in
user)".
regards, tom lane
Adrian Klaver writes:
> On 10/21/21 09:53, Tom Lane wrote:
>> I'm not sure if we want to change a security-relevant behavior
>> in released branches. But if we don't, we probably need to
>> change the docs to something like "(by default, the logged-in
>>
plicitly say which role
it's intending to set the password for:
db=> \password
Enter new password for role "dbowner":
Enter it again:
regards, tom lane
el
realizes that the connection is lost. The actual blame probably resides
with some firewall or router that has a short timeout for idle
connections.
I'd try turning on fairly aggressive TCP keepalive settings for the
connection, say keepalives_idle=30 or so.
regards, tom lane
--
22-Oct-2021 +00:00
(1 row)
Regardless of whether the original choice not to have this variant
was intentional or an oversight, I'd be pretty loath to change it now
because of backwards compatibility. But Postgres is adaptable.
regards, tom lane
Ninad Shah writes:
> Would keepalive setting address and mitigate the issue?
[ shrug... ] Maybe; nobody else has more information about this
situation than you do. I suggested something to experiment with.
regards, tom lane
ed.
Did the underlying OS version change? If so, see
https://wiki.postgresql.org/wiki/Locale_data_changes
regards, tom lane
. While a real
feature patch that made this optional would be a bit complicated,
doing it unconditionally should be a one-line change.
regards, tom lane
Tore Halvorsen writes:
> That would be appending it for "pg_catalog.pg_get_constraintdef(oid) AS
> condef" in getConstraints in pg_dump.c?
No, you want to mess with the text printed by dumpConstraint().
regards, tom lane
lgorithm
automatically prints the minimum number of digits needed to ensure
exact reload.
All the same comments apply to float8, of course, with a
different number of digits.
regards, tom lane
d224008ef98;hb=HEAD
regards, tom lane
27;baz
regression=# \set foo bar ''baz
regression=# \echo :foo
barbaz
Not sure offhand how well-documented this is.
regards, tom lane
he output; though you'd have to provide
a layer of dequoting and line-separation logic.
regards, tom lane
[1] https://www.postgresql.org/docs/current/libpq-single-row-mode.html
to_date
---
0044-03-15 BC
(1 row)
I'd be the first to agree that that code is a mess and could stand to
be rewritten --- but I seriously doubt that we'd take a patch that
intentionally breaks cases that work fine today. There's also the
angle that these are supposed to be Oracle-compatible, so I wonder
what Oracle does with such input.
regards, tom lane
principle this'll produce
a far better estimate than any fixed default could provide. If
you're coming out with a crappy estimate, you might be able to
improve matters by increasing the column's statistics target so
that more MCV and histogram entries are collected.
w
the negative-century case works, so it seems sane to do likewise.
The last two look like a parsing issue: with no field separator
(nope, the space doesn't count), the code is taking the dash
as a field separator.
regards, tom lane
, table or function) I would be
> more than happy.
application_name is meant for that ...
regards, tom lane
Mitar writes:
> Anyone? Any way to determine the number of affected rows in a statement
> trigger?
Check the size of the transition relation.
regards, tom lane
al of data down the pipe.
You should keep pulling (and discarding) data until you get the end
or error response.
regards, tom lane
ocument the implications for logical replication better.
regards, tom lane
e that namespace is one of
>the properties sent with the types)?
Some would say that custom types are THE defining feature of
Postgres, compared to other SQL implementations.
regards, tom lane
share the
* same lexer. If you add/change tokens here, fix PL/pgSQL to match!
Since you didn't do that, PL/pgSQL is confused about the token codes
in use for DOT_DOT and so on.
regards, tom lane
pected. What minor release are you using?
regards, tom lane
=?UTF-8?B?0JTQvNC40YLRgNC40Lkg0JjQstCw0L3QvtCy?= writes:
> вс, 14 нояб. 2021 г. в 22:31, Tom Lane :
>> Usually this is caused by being careless about search_path assumptions
>> in your functions ... but with no details, it's impossible to say
>> anything with certainty.
=?UTF-8?B?0JTQvNC40YLRgNC40Lkg0JjQstCw0L3QvtCy?= writes:
> вс, 14 нояб. 2021 г. в 23:46, Tom Lane :
>> This is not "details", this is an evidence-free assertion. Please show
>> a concrete example of problematic functions.
> Ok, I'll do it.
> [ example ]
g(DEBUG) after every line of code
in the server, and then high-level debugging logs would be even more
impossibly voluminous than they are now. I'd say the existing logging
gave you plenty of clue where to look.
regards, tom lane
=?UTF-8?B?0JTQvNC40YLRgNC40Lkg0JjQstCw0L3QvtCy?= writes:
> Is it ok to attach a 5MB data schema or is it not possible? Copy one by one
> to a letter?
Is it smaller if you omit the data (-s switch)? Shouldn't be relevant
here.
regards, tom lane
onally so I would like to confirm.
We did improve matters in v14, see
https://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=3d351d916
(It looks like the v14 release notes say nothing about this, which perhaps
was a bad idea.)
regards, tom lane
ter. I wouldn't worry about cleaning up
unnecessary EXECUTE usage till you have a working port.
regards, tom lane
ou a better feeling for the work that
the plpgsql function has to do.
3. I kind of doubt that outputting a single text column
is really the end result you want here. How is it
meaningful to be crosstab'ing multiple questions if
that's to be the end result?
regards, tom lane
ep_by_space = 0 '\000', int_n_cs_precedes = 0 '\000',
int_n_sep_by_space = 0 '\000', int_p_sign_posn = 0 '\000',
int_n_sign_posn = 0 '\000'}
A quick lookup later, that's U+202F or "narrow no-break space".
cash_in is picky about this, and won't take plain ASCII space as
a substitute. Not sure if it should.
regards, tom lane
1, DEFAULT, 1);
I wonder what version of pg_dump is actually being used there.
regards, tom lane
27; for the generated column, but
maybe we don't?
regards, tom lane
Ron writes:
> On 11/20/21 11:01 AM, Tom Lane wrote:
>> A quick lookup later, that's U+202F or "narrow no-break space".
>> cash_in is picky about this, and won't take plain ASCII space as
>> a substitute. Not sure if it should.
> It probably sh
ave me the clue I needed. If there are dropped
column(s) before the GENERATED one, pg_dump gets it wrong ---
but only in --inserts mode, not the default COPY mode, which
no doubt explains why nobody noticed. There is code in there
to inject DEFAULT, but it must be indexing the flag array wrong.
Will fix, thanks for the report!
regards, tom lane
2401 - 2500 of 2962 matches
Mail list logo
