;s
something that kept it from starting, but there's no evidence
offered here about what.
regards, tom lane
an example, using
psql -c '\pset format unaligned' -c "SELECT * FROM unary" -o /dev/null
this example drops from ~16s to ~1.7s on my machine.
regards, tom lane
Mark Mizzi writes:
> So to confirm, EXPLAIN ANALYZE does not detoast rows?
Not values that would have been transmitted to the client, no.
regards, tom lane
pedantic as well
as confusing. Should we leave those usages alone, or reduce them
to just "superuser"?
regards, tom lane
tion set.
I think it's most likely a bug in parquet_fdw. We have tests of similar
cases in postgres_fdw, and they work fine.
regards, tom lane
know
> add those in pg_proc.dat and pg_am.dat, if it won't create access method for
> spb, what else I need to
> do?
To be very blunt, it doesn't sound to me that your skills with
Postgres are anywhere near up to the task of writing a new
index access method. You should start with some less-ambitious
project to gain some familiarity with the code base.
regards, tom lane
'::interval
+ h.i * '1 hour'::interval
(Or you can spell the constants like INTERVAL '1 day',
if you prefer.)
regards, tom lane
nt is to keep five years' worth of records
and you want to drop the oldest month's records in bulk once a month,
then partitioning by month would be pretty helpful to make those drops
cheap. That would lead to 60 active partitions which is entirely
reasonable.
regards, tom lane
l_drop event trigger:
I wonder whether the OP's purposes wouldn't be better served by
making B a view or materialized view, instead of a separate table.
Then the cascaded-drop behavior would be automatic.
regards, tom lane
hose to skip that
object, or that one of them mistakenly thought that the CREATE
FUNCTION should be issued first. None of those things should happen
AFAICS, and none of them does happen in a simple test case, so
there's something going on here that we've not been shown.
regards, tom lane
e client-side replication logic. I suggest
finding where it's coming from and then asking the appropriate authors.
regards, tom lane
any reasonable way to proceed from here?
contrib/amcheck might help to identify the faulty data (at this
point there's reason to fear multiple corruptions ...). If
you're running v14 or v15 there's a frontend for that called
pg_amcheck.
regards, tom lane
is to try using contrib/pageinspect to examine each
page of the table. Its output is just gobbledegook to most people,
but there's a good chance it'd fail visibly on the corrupted page(s).
Also, please read
https://wiki.postgresql.org/wiki/Corruption
if you didn't already.
regards, tom lane
an't be real
sure that you identified and cleared all the data corruption.
regards, tom lane
Ron writes:
> On 11/7/22 08:02, Tom Lane wrote:
>> call. It'd still be recommendable to pg_dumpall and restore into
>> a freshly-initdb'd cluster, because otherwise you can't be real
>> sure that you identified and cleared all the data corruption.
> Why *
exactly? Has anything else changed?
> I could also get a little information from gdb, I'm not sure if it will
> help:
This looks pretty messed up. Are you sure the debug symbols you're using
match the package?
Even better, can you construct a self-contained test case?
regards, tom lane
below another Append. That
shouldn't happen AFAIK --- the planner tries to collapse out such
cases. Can you get us an EXPLAIN for the problem query?
regards, tom lane
Willian Colognesi writes:
> No, the database is running well, no problem until now after disabled *jit.*
Interesting. Which version of LLVM is installed?
regards, tom lane
awhile to see if that really fixed it.
regards, tom lane
is the best answer for you --- it's hard to
say when we'll be able to make progress with this, given the lack of
reproducible test cases.
regards, tom lane
tables) the generic plan is pretty much always going to lose. That
doesn't bother me enormously --- there are other query patterns
with similar behavior.
If you know that your queries always need custom plans, I question
the value of using PREPARE at all.
regards, tom lane
Whether you consider
that elegant is up to you ;-) ... but it should work, as long as
you don't break things by renaming the enum's values.
regards, tom lane
e from pg_type t left join pg_attribute a
on t.typrelid = a.attrelid where typname = 'complex' order by attnum;
attname | atttypid
-+--
r | double precision
i | double precision
(2 rows)
regards, tom lane
and it will likely have associated entries in other catalogs that a
composite type won't. But the core catalog entries are about the same.
regards, tom lane
Rob Sargent writes:
> Short version: Does a current version of postgres tolerate ascii dumps
> from older versions?
We intend it to. Have you got an actual problem?
regards, tom lane
d "pg_ctl reload".
regards, tom lane
onder what you get from psql with the same command
line option sets.
regards, tom lane
Corruption
but in general the news is not going to be good.
regards, tom lane
here. Nonetheless,
your path forward is clear: use pg_dump (or better pg_dumpall)
and then load the output into a freshly initdb'd v12 installation.
It'll be a bit slower than the pg_upgrade way, but it'll work.
regards, tom lane
Igor Korot writes:
> Is there a printf-lilke function inside libpq that can be used
> to create a proper query string with the proper quoting of the
> literal?
No. You'd be better off to transmit the literal value as a
parameter.
regards, tom lane
that the older
pg_dump might have bugs that are fixed in the newer version. But
such bugs are rare, so usually it'll work fine to use the older one.
We do endeavor to make sure that older dump output will load into
newer versions, because in disaster-recovery scenarios an older
dump might be all you have.
regards, tom lane
a bit of a foot-gun. I wonder if we should make
it safer by insisting that the resolved index be partial when there's
a WHERE clause here. (This documentation text is about as clear as
mud, too. What does "inferred" mean here? I think it means "chosen as
arbiter index", but maybe I misunderstand.)
regards, tom lane
ncy
is preserved; without it we can't offer any guarantees.
regards, tom lane
quest didn't
get queued for lack of in-kernel memory space ... in which case
"nothing happened".
regards, tom lane
rg/docs/current/ecpg-commands.html#ECPG-TRANSACTIONS
regards, tom lane
(firstname character(30), ...);
create function testfunction() returns setof testfunction_result as ...
regards, tom lane
the like)?
regards, tom lane
ally with
> dynamic queries in PL/pgsql.
Yeah --- I fear there is no hope of making a feature like this that
works reliably enough that we'd accept it. pg_restore is just not
that smart about what is in the chunks of DDL that it processes,
and trying to make it smart enough is a losing game.
regards, tom lane
his at dump time, you could just
temporarily rename the schema on the server while making the dump.
(Not that that would fix function bodies either.)
regards, tom lane
ostmaster log.
regards, tom lane
e any basis for filing a bug report.
regards, tom lane
check.
It might or might not be related to the node-type error.
regards, tom lane
shashidhar Reddy writes:
> Extension version is 2.2. Is anything need to be done? To fix this issue?
You could start by getting the information I asked for, ie where is
this error being thrown from according to log_error_verbosity=verbose.
regards, tom lane
headers. But you'd expect that it largely
wouldn't work at all if so.)
regards, tom lane
polygon,polygon) |9 | search
gist | poly_ops| <<|(polygon,polygon) | 10 | search
gist | poly_ops| |>>(polygon,polygon) | 11 | search
gist | poly_ops| |&>(polygon,polygon) | 12 | search
gist | poly_ops| <->(polygon,point) | 15 | ordering
(13 rows)
regards, tom lane
get rid of this role without revoking xxx in all
> the individual DB objects he has privs too? Something like "drop role xxx
> cascade" ?
Not directly, but see REASSIGN OWNED and DROP OWNED.
regards, tom lane
Ron writes:
> On 11/17/22 17:16, Tom Lane wrote:
>> Not directly, but see REASSIGN OWNED and DROP OWNED.
> SIMULATE and VERBOSE options would be handy, to see what those commands
> would actually do.
Hmph. I'm surprised to realize that those commands don't produce
t
f
any such settings. I can't offhand think of any other server-side
configuration that would do it.
regards, tom lane
would have much
real-world significance if we did calculate it.
You could argue that read() is also squishy since it might be satisfied
from kernel buffers rather than actually getting bits off the disk.
However, there is some physical reality there: before read() we did
not have the data, and afterwards we do.
regards, tom lane
ariation in how you spell it, where you can write it,
and so on. My own inclination is to not do anything here until/unless
the SQL committee standardizes something, because there's too much
risk of finding ourselves incompatible with the standard.
regards,
map is
fully set. Did you vacuum this table after building it, or wait
long enough for autovacuum to do so?
regards, tom lane
ain it ... what applied that setting?
regards, tom lane
a linear sort order if we want to support btree indexes or ORDER BY
on intervals.
You can use justify_hours() to get from '26 hours' to '1 day 2 hours'.
I'm not sure if there's a compact way to go the other direction,
though you could always use extract() to get the components and
sum them up.
regards, tom lane
to buy into maintaining our own fork of Readline, if that's
what you're trying to suggest. If it "just works" now, that'd be great.
Otherwise, maybe you should be speaking to the Readline maintainers
about what changes are needed in it?
regards, tom lane
. From a security standpoint, they're implementation
details of the table they belong to. Whatever usage restrictions
you want should be put on the table, instead.
regards, tom lane
stgresql.org/docs/current/indexes-multicolumn.html
That is, tid and tidh need to be the first two index columns.
regards, tom lane
to INHERITS-style partitioning.
regards, tom lane
Andres Freund writes:
> On 2022-11-21 10:58:06 -0500, Tom Lane wrote:
>> It'd certainly be nice if we could use Readline on Windows.
> 2) The last time I checked, msvc couldn't preprocess tab-complete.c with
>USE_READLINE defined, due to running into some pr
Andres Freund writes:
> On 2022-11-23 18:11:22 -0500, Tom Lane wrote:
>> Huh ... do you recall the details? Large as tab-complete is, it's
>> far smaller than gram.y:
> So It might just be that we need to split up that very long "else if" chain in
> psql_com
e an easy quick-fix. We'd need to refactor so that
the cleanup housekeeping at the bottom of psql_completion() was in a
wrapper function, but perhaps that wouldn't be too messy.
regards, tom lane
s sorted by trans_dttm anyway.
You might try making extended stats on these three columns to see
if that helps the planner to get a better rowcount estimate.
If it understood that there were fewer than 50 matching rows,
it might opt for the use-the-=ANY-and-sort plan type.
regards, tom lane
as big as you want
on any reasonably modern machine. I wouldn't blink at a few
million locktable entries, at least not on 64-bit hardware.
regards, tom lane
stated command?
BTW, it's pretty hard to believe that you need to use sudo
for this.
regards, tom lane
BY id
LIMIT 1
FOR UPDATE
SKIP LOCKED
)
DELETE FROM queue
WHERE id IN (SELECT * FROM target_rows)
RETURNING *;
regards, tom lane
those enabled.
> Is there anything else I want from initdb?
If you can connect to the new installation, then you're done with
that part, and can get on with the dump-and-restore part.
regards, tom lane
imes worth
saying "database superuser" to ensure that you don't confuse people
who might think of some external-to-Postgres meaning of "superuser",
but otherwise plain "superuser" is fine. And we've settled on
"bootstrap superuser" as the best term for the role with OID 10.
So the present set of glossary entries looks fine to me.
regards, tom lane
Harmen writes:
> On Mon, Nov 28, 2022 at 12:11:53PM -0500, Tom Lane wrote:
>> So basically it's unsafe to run the sub-select more than once,
>> but the query as written leaves it up to the planner whether
>> to do that. I'd suggest rephrasing as [...]
> I'
tstzmultirange | | func
pg_catalog | tstzmultirange | tstzmultirange | VARIADIC tstzrange[] | func
pg_catalog | tstzmultirange | tstzmultirange | tstzrange | func
(3 rows)
My guess is that your server is not in fact PG14, but some
older version.
regards, tom lane
se it couldn't be maintained
in a reasonable way.
I think the answer here is "don't use the new syntax if you want the
function body stored textually". You can have one set of benefits,
or the other set, but not both at once.
regards, tom lane
hat's
to stop the same actors from examining/modifying other configuration
files, or even the actual database contents? If you don't think your
data directory is secure, you have problems that Postgres can't fix.
regards, tom lane
ee the contradictions in this? You want the
database to preserve the original DDL, but you also want it to update
in response to subsequent alterations. You can't have both those
things.
regards, tom lane
t telling so, but don't change the text
> until next alter procedure is run.
I'm astonished at the number of people who think that poorly-implemented
Oracle behavior is something we should emulate.
regards, tom lane
matviews and
sequences. Some other projects adopt more liberal views about
what should be shown in those views, but that one is our policy.
regards, tom lane
not a Postgres question. Maybe you need
an explicit fflush-equivalent step? Dunno.
regards, tom lane
dump has
some heuristics for dealing with such cases, but maybe it needs more.
Please create a self-contained example and submit it to pgsql-bugs.
regards, tom lane
rts of
misbehaviors might ensue from that, but I'm pretty certain that the
data in the GENERATED column after dump/restore won't match what
you had there beforehand.
regards, tom lane
better look at the in_extension fields of CollectedCommands.
I don't think we expose that state at the SQL level, but it's pretty
hard to make a useful event trigger without writing any C ...
regards, tom lane
have
seen associated errors earlier in the build log.
regards, tom lane
e certain that you applied the configuration change to
your new installation?
It'd be worth doing
show tcp_keepalives_idle;
in one of the sessions where you are having trouble. And maybe
check the other keepalives settings too?
regards, tom lane
(3 rows)
regards, tom lane
r I'd expect on a local (Unix-socket) connection
... you sure you're doing this from one of the problematic clients?
regards, tom lane
to making pg_dump emit the more modern
spelling (I think ... you'd need to look into pg_restore to make sure
it's not assuming something in this area). But doing that won't
really remove the hazard.
regards, tom lane
s 'exampl', then only the first of
these will match. So IMO the question is not "why is it failing
on prod?", it's "how the heck did it work on the other machine?".
You won't get nice results if websearch_to_tsquery is using a
different TS configuration than to_tsvector did.
regards, tom lane
date is 8333 by my math.
Looking at the bit-pattern for 1,466,004,328: 0x57617368, it seems
totally unrelated, more like ASCII text ("Wash") than anything else.
You sure you're reading the right column of the result?
regards, tom lane
described in
> 8.1 Numeric Types).
Our integers do not have infinities, nor NaNs.
Anyway, I don't mean to slam the door on this idea completely,
but I think you need to make a much better-supported argument
for it.
regards, tom lane
uot;pin" entries.
regards, tom lane
;s not allowing non-credentialed logins from anywhere. And
for pete's sake don't use a guessable password.
regards, tom lane
generate
ALTER VIEW v ALTER COLUMN c SET DEFAULT LOCALTIMESTAMP(0)
commands doesn't do what you need?
regards, tom lane
inner or has the indexed table on the nullable side.
We have no support for nestloop right join, which is what would be
needed to make things run fast with no index on B.
regards, tom lane
closer to shared_buffers, as it seems to indicate that fetches
from kernel space are pretty expensive on your platform.)
regards, tom lane
h to do that. If you have a constant stream of readers
it will never be able to get that lock. You'll need to find a way to
momentarily block those readers.
regards, tom lane
don't think there's any ready way to discover that from SQL level.
regards, tom lane
references (index scan, seq scan, etc) during deletes (and
> inserts and updates)?
No, not directly, but you could look at EXPLAIN ANALYZE to see which
of the RI triggers is eating the time. It's not going to be hard to
figure out which one(s) are using indexed plans and which are not.
regards, tom lane
Ron writes:
> On 1/12/23 00:07, Tom Lane wrote:
>> No, not directly, but you could look at EXPLAIN ANALYZE to see which
>> of the RI triggers is eating the time.
> Good to know, but even deleting one day of data (90,000 rows using an index
> scan on the date field) takes
croseconds. If we're forced to interconvert
between those units, we use 30 days = 1 month and 24 hours = 1 day,
but it's usually best to avoid doing that.
regards, tom lane
t()
keywords to make that easier.
regards, tom lane
is not what Ken is after IIUC.
regards, tom lane
a different set of failure modes. It'd be
particularly bad if you have more than one standby, because you could
easily get into a situation where *none* of the nodes represent truth.
regards, tom lane
rming the sort using an index scan instead of
a bespoke sort step. AFAIR, cost_sort doesn't consider random_page_cost
at all, and neither does cost_hashjoin.
regards, tom lane
t by setting up "extended statistics" on that pair of columns:
https://www.postgresql.org/docs/current/planner-stats.html#PLANNER-STATS-EXTENDED
(I might be wrong, but I think that will help even when one of
the troublesome conditions is a null-check. If it doesn't, then
we have something to improve there ...)
regards, tom lane
1301 - 1400 of 2962 matches
Mail list logo
