Re: Clarification on the release notes of postgresql 12 regarding pg_upgrade

On Fri, Oct 4, 2019 at 09:43:31AM -0700, Peter Geoghegan wrote: > On Fri, Oct 4, 2019 at 9:09 AM Tom Lane wrote: > > > You can't REINDEX safely regarding that note. > > > > Actually running into that problem is quite unlikely; and if you did > > hit it, it'd just mean that the REINDEX fails, not

Event Triggers and Dropping Objects

The event trigger firing matrix lists tags like DROP TABLE and DROP FUNCTION are listed below the ddl_command_end event, but when I created a basic audit table and event trigger, they don't seem to fire. I know sql_drop exists, but again the matrix lists DROP commands in the ddl_command_end event.

Re: Pg11 -- MultiXactId xxxx has not been created yet -- apparent wraparound

On 2019-Oct-04, Moreno Andreo wrote: > Il 04/10/19 18:28, Alvaro Herrera ha scritto: > > I wonder if it would work to just clear that multixact with > > SELECT ... WHERE ctid='(3160,31)' FOR UPDATE > select ...what? :-) Sorry but it's totally beyond my knowledge and my > control after resolvin

Re: PG11 Parallel Thanks!!

On Thu, Oct 3, 2019 at 10:31 AM Jason Ralph wrote: > The end of month process that we run at my company was a pg_dump and > pg_restore of 3 tables, these tables are around ~(400GB) each. The entire > process on pg93 took 29 hours. > > The index creation portion of the restore on the target pg9.

Re: Postgres 12: backend crashes when creating non-deterministic collation

Thomas Kellerer writes: > Tom Lane schrieb am 04.10.2019 um 16:52: >> No, this thread is a sufficient report. What *would* be a good use >> of time is to get a stack trace from the crash, if you can. > I don't know if I did everything correctly, but here it is. I hope it helps >> icuuc53.dll!00

Re: Postgres 12: backend crashes when creating non-deterministic collation

Tom Lane schrieb am 04.10.2019 um 16:52: Is it worth the effort to report that through the bug reporting form? No, this thread is a sufficient report. What *would* be a good use of time is to get a stack trace from the crash, if you can. I don't know if I did everything correctly, but here i

Re: psql \copy hanging

On 10/4/19 12:19 AM, Arnaud L. wrote: Le 03/10/2019 à 16:32, Adrian Klaver a écrit : I may have missed it before, but where is the Postgres server located? On the same local area network. Not on the computer running the script (so direct COPY TO is not an option). Given that this seems t

Re: Postgres 12: backend crashes when creating non-deterministic collation

Daniel Verite schrieb am 04.10.2019 um 18:49: Creating works, but apparently on Windows ICU does not support this. After installing v12 on windows with the EDB installer, I notice that it ships with ICU 53, a relatively old version (2014). Concerning the problem just above (not the crash), ICU

Re: Pg11 -- MultiXactId xxxx has not been created yet -- apparent wraparound

Il 04/10/19 18:28, Alvaro Herrera ha scritto: I wonder if it would work to just clear that multixact with SELECT ... WHERE ctid='(3160,31)' FOR UPDATE select ...what? :-) Sorry but it's totally beyond my knowledge and my control after resolving the issue i'll surely go and search docs to un

Re: Postgres 12: backend crashes when creating non-deterministic collation

Thomas Kellerer wrote: > > To get a case insensitive collation you'd have to use something like > > > > LOCALE = 'de-DE-u-ks-level2' > > Creating works, but apparently on Windows ICU does not support this. After installing v12 on windows with the EDB installer, I notice that it ship

Re: Clarification on the release notes of postgresql 12 regarding pg_upgrade

On Fri, Oct 4, 2019 at 9:09 AM Tom Lane wrote: > > You can't REINDEX safely regarding that note. > > Actually running into that problem is quite unlikely; and if you did > hit it, it'd just mean that the REINDEX fails, not that you have any > urgent problem to fix. I'd encourage you to just go ah

Re: Pg11 -- MultiXactId xxxx has not been created yet -- apparent wraparound

On 2019-Oct-04, Moreno Andreo wrote: > Il 04/10/19 17:30, Alvaro Herrera ha scritto: > > On 2019-Oct-04, Moreno Andreo wrote: > > > > > select * from heap_page_items(get_raw_page('tablename',3159)); > > > select * from heap_page_items(get_raw_page('tablename',3160)); > > > > > > and so on for ab

Re: Clarification on the release notes of postgresql 12 regarding pg_upgrade

=?UTF-8?B?Sm9zZWYgxaBpbcOhbmVr?= writes: > But the problem in this case is probably this note: >> This means that a REINDEX >> operation on an >> index pg_upgrade'd from a previous release could potentially fail. > You can't REINDEX safely reg

Re: Pg11 -- MultiXactId xxxx has not been created yet -- apparent wraparound

Il 04/10/19 17:30, Alvaro Herrera ha scritto: On 2019-Oct-04, Moreno Andreo wrote: select * from heap_page_items(get_raw_page('tablename',3159)); select * from heap_page_items(get_raw_page('tablename',3160)); and so on for about 5 or 6 pages. Please paste the output of that for pages 3159 and

Re: Pg11 -- MultiXactId xxxx has not been created yet -- apparent wraparound

On 2019-Oct-04, Moreno Andreo wrote: > select * from heap_page_items(get_raw_page('tablename',3159)); > select * from heap_page_items(get_raw_page('tablename',3160)); > > and so on for about 5 or 6 pages. Please paste the output of that for pages 3159 and 3160, as well as the output of pg_contro

Re: Perl DBI converts UTF-8 again to UTF-8 before sending it to the server

## Matthias Apitz (g...@unixarea.de): > my $text = "ä \xc3\xa4"; That will only work if you remove "use utf8". And then other stuff may break. Regards, Christoph -- Spare Space

Re: Perl DBI converts UTF-8 again to UTF-8 before sending it to the server

El día viernes, octubre 04, 2019 a las 04:29:32p. m. +0200, Christoph Moench-Tegeder escribió: > ## Matthias Apitz (g...@unixarea.de): > > > my $text = "\xc3\xa4"; > > print "text: ".$text."\n"; > > Your output is lying to you: > you need a binmode(STDOUT, ':encoding(utf8)'), which will make th

Re: Postgres 12: backend crashes when creating non-deterministic collation

On 2019-10-04 10:52:38 -0400, Tom Lane wrote: > Thomas Kellerer writes: > > It also works on Windows when I specify "correct" locale names - the above > > seems to be an edge case. > > Is it worth the effort to report that through the bug reporting form? > > No, this thread is a sufficient repor

Pg11 -- MultiXactId xxxx has not been created yet -- apparent wraparound

Hi all,     I'm encountering this issue in a Windows 10/Pg11.5 I followed the thread @ https://postgrespro.com/list/thread-id/2380690 but examining heap pages is far beyond my knowledge, so if any of the gurus would spend some time on it, I would be ver

Re: Postgres 12: backend crashes when creating non-deterministic collation

Thomas Kellerer writes: > It also works on Windows when I specify "correct" locale names - the above > seems to be an edge case. > Is it worth the effort to report that through the bug reporting form? No, this thread is a sufficient report. What *would* be a good use of time is to get a stack t

Re: Postgres 12: backend crashes when creating non-deterministic collation

Am 04.10.19 um 12:13 schrieb Thomas Kellerer: I was trying to learn how the new non-deterministic collations in v12 work, but the following makes the backend crash: CREATE COLLATION de_ci (provider = icu, locale = 'de-x-icu', deterministic = false); Which leads to: 2019-10-04 11:54:23 CE

Re: Postgres 12: backend crashes when creating non-deterministic collation

Thomas Kellerer wrote: > It also works on Windows when I specify "correct" locale names - the above > seems to be an edge case. > Is it worth the effort to report that through the bug reporting form? Sure. Both the crash with 'de-x-icu' and the difference in behavior between Linux and Win

Unexpected plan width with foreign data wrapper

Hello. I work on fixing test suite (make installcheck) failures in Multicorn project[1]. The purpose of project is to make it possible to write Foreign Data Wrappers[2] in Python, not in C[3]. Test suite executes SQL statements with psql and compares output with expected. If they are different, t

Re: Perl DBI converts UTF-8 again to UTF-8 before sending it to the server

## Matthias Apitz (g...@unixarea.de): > my $text = "\xc3\xa4"; > print "text: ".$text."\n"; Your output is lying to you: you need a binmode(STDOUT, ':encoding(utf8)'), which will make this print "ä", and a utf8::decode($text), after which you get "ä". And when you pass that $text through DBD::Pg

Re: Postgres 12: backend crashes when creating non-deterministic collation

Laurenz Albe schrieb am 04.10.2019 um 16:04: I was trying to learn how the new non-deterministic collations in v12 work, but the following makes the backend crash: CREATE COLLATION de_ci (provider = icu, locale = 'de-x-icu', deterministic = false); Which leads to: 2019-10-04 11:54:23 CEST LO

Re: Clarification on the release notes of postgresql 12 regarding pg_upgrade

But the problem in this case is probably this note: > This means that a REINDEX operation on an index pg_upgrade'd from a previous release could potentially fail. You can't REINDEX safely regarding that note. pá 4. 10. 2019 v 16:06 odesílatel

Re: Clarification on the release notes of postgresql 12 regarding pg_upgrade

On Fri, 2019-10-04 at 10:00 -0300, Marcelo Lacerda wrote: > There are a few instances where the release notes seem to indicate > that the administrator should use pg_dump to upgrade a database so > that improvements on btree can be available. > > Here are they: > > 1. > > >In new btree indexes,

Re: Postgres 12: backend crashes when creating non-deterministic collation

Thomas Kellerer wrote: > I was trying to learn how the new non-deterministic collations in v12 > work, but the following makes the backend crash: > > CREATE COLLATION de_ci (provider = icu, locale = 'de-x-icu', > deterministic = false); > > Which leads to: > > 2019-10-04 11:54:23 CEST LOG: se

Re: BitmapAnd on correlated column?

greigwise writes: > Granted it cannot skip the condition, but that doesn't mean that it has to > use that second index. It's doing a recheck on the conditions anyway, > right? I'm afraid you're out of luck on that for now. In principle, the presence of extended stats on the column combination

Perl DBI converts UTF-8 again to UTF-8 before sending it to the server

Hello, We're facing the problem that UTF-8 data to be INSERT'ed into a CHAR column is converted again to UTF-8, assuming it's ISO. I have here a small Perl program which can be used for testing: #!/usr/local/bin/perl use utf8; my $PGDB = 'dbi:Pg:dbname=newsisis;host=127.0.0.1'; my $PGDB_

Re: BitmapAnd on correlated column?

>Extended statistics will tell PostgreSQL that it is very unlikely >that the first condition will contribute significantly, but that >is no proof that the condition can be omitted, so the optimizer >cannot just skip the condition. Granted it cannot skip the condition, but that doesn't mean that it

Clarification on the release notes of postgresql 12 regarding pg_upgrade

There are a few instances where the release notes seem to indicate that the administrator should use pg_dump to upgrade a database so that improvements on btree can be available. Here are they: 1. >In new btree indexes, the maximum index entry length is reduced by eight bytes, to improve handlin

Postgres 12: backend crashes when creating non-deterministic collation

I was trying to learn how the new non-deterministic collations in v12 work, but the following makes the backend crash: CREATE COLLATION de_ci (provider = icu, locale = 'de-x-icu', deterministic = false); Which leads to: 2019-10-04 11:54:23 CEST LOG: server process (PID 7540) was terminated

Re: BitmapAnd on correlated column?

On Thu, 2019-10-03 at 14:22 -0700, greigwise wrote: > I'm running the following query on Postgres version 10.8: > > SELECT count(*) FROM test_table WHERE and id_column_1 IN (9954, > 9690, 9689, 9688) AND id_column_2 IN ([long list]); > > There are 2 indexes, one on id_column_1 and one on id_col

Re: Archive_clean

On Fri, 2019-10-04 at 13:34 +0530, Sonam Sharma wrote: > I have enabled the archiving and the archive path is getting filled > frequently. can I delete the logs from archive path after taking > backup ? Sure, just make sure you delete nothing that is still needed to recover from the backup. pg_a

Archive_clean

I have enabled the archiving and the archive path is getting filled frequently. can I delete the logs from archive path after taking backup ?

Re: psql \copy hanging

Le 03/10/2019 à 16:32, Adrian Klaver a écrit : I may have missed it before, but where is the Postgres server located? On the same local area network. Not on the computer running the script (so direct COPY TO is not an option). Given that this seems to be some sort of resource issue and th