could not read block 0 in file : read only 0 of 8192 bytes when doing nasty on immutable index function

I've found this strange (to me) behavior when doing nasty things with indexes and immutable functions: create table t( pk serial, t text ); insert into t( t ) values( 'hello' ), ('world'); create or replace function f_fake( i int ) returns text as $body$ declare v_t text; begin select t into

Re: could not read block 0 in file : read only 0 of 8192 bytes when doing nasty on immutable index function

On Wed, Jun 27, 2018 at 10:44 PM Andres Freund wrote: > But I also can't reproduce it either on 10.4, 10-current, master. Did > you build from source? Packages? Any extensions? Is there anything > missing from the above instruction to reproduce this? Somehow today I cannot reproduce it by myself

Re: could not read block 0 in file : read only 0 of 8192 bytes when doing nasty on immutable index function

Got it: it happens if you drop and recreate the index. It shows up either setting max_parallel_maintanance_workers to zero or a greater value. testdb=> create table t( pk serial, t text ); CREATE TABLE testdb=> insert into t( t ) values( 'hello' ), ('world'); INSERT 0 2 testdb=> create or replace

help understanding create statistic

Hi all, in order to better understand this feature of 10, I've created a table like this: CREATE TABLE expenses( ... day date, year int, CHECK( year = EXTRACT( year FROM day ) ) ); so that I can ensure 'year' and 'day' are tied together: SELECT count(*) FILTER( WHERE year = 2016 ) AS

Re: plperl and plperlu language extentsions

On Thu, Jun 28, 2018 at 9:26 AM Niles Oien wrote: > > Yet plperl would seem to be installed : > > # yum list | grep postgres | grep perl > postgresql-plperl.x86_64 9.2.23-3.el7_4 base > postgresql10-plperl.x86_6410.4-1PGDG.rhel7 pgdg10 >

select version() with internal number version?

HI all, I see a lot of external tools that do a 'SELECT version()' and then parse the output to get the version number (e.g., 10.4). My opinion is that this is not a good approach, since the output of version includes a lot of information and can mismatch a poorly written regular expression. It wou

Re: select version() with internal number version?

On Thu, Aug 9, 2018 at 3:51 PM Tom Lane wrote: > Also, if you're using libpq, see PQserverVersion() which > (a) avoids a round trip to the server, and (b) works further back > than server_version_num, though that issue is probably academic > to most folk at this point (server_version_num appeared

help defining a basic type operator

Hi all, I'm trying to define a custom data type that would represent a number of bytes in a lossy human way. The type is defined as: typedef struct HFSize { double size; int scaling; } HFSize; and the operator function is defined as: Datum hfsize_add(PG_FUNCTION_AR

Re: help defining a basic type operator

On Mon, Aug 20, 2018 at 4:51 PM Tom Lane wrote: > > Luca Ferrari writes: > > I'm trying to define a custom data type that would represent a number > > of bytes in a lossy human way. > > You did not show us the SQL definition of the type. I don't see anything &g

help understanding pgbench results

Hi all, I'm trying to understand some simple benchmarks but I need an hint. =# select version(); version - PostgreSQL 11.1 on x86_64-pc-linux-gn

Re: FATAL: invalid page in block 0 of relation global/1262

On Fri, Jul 12, 2019 at 5:22 PM Ibrahim Edib Kokdemir wrote: > > Hi, > I upgraded my cluster from 9.6 to 11 with pg_upgrade hardlink parameter two > days ago. Since then it has been working great as v11. Today while running > heavy update queries on it, I got the "FATAL: invalid page in block

Re: Issue: Creating Symlink for data directory of postgresql in CentOS7

On Fri, Jul 12, 2019 at 4:47 PM Laurenz Albe wrote: > So there is some "postgresql-check-db-dir" (not part of PostgreSQL) that > complains > that there is nothing in /data/postgresql/data. Is that accurate? > According to this

Re: Issue: Creating Symlink for data directory of postgresql in CentOS7

On Sat, Jul 13, 2019 at 5:12 AM Chatterjee, Shibayan wrote: > For sure there's all the necessary files in '/data/postgresql/data'. The > startup process cannot read it, because of sym link. > Executing init-db return the below message: > I suspect this could be a systemd problem. What if you try

Re: Testing an extension against multiple versions of Postgresql

On Sat, Jul 13, 2019 at 8:03 AM Ian Barwick wrote: > > So the questions are: > > Can I have make install & make installcheck run against multiple versions > > at the same time or how do I tell the installcheck to run against the 11 > > server. > > Try setting relevant environment variables [*] f

Re: Issue: Creating Symlink for data directory of postgresql in CentOS7

On Sun, Jul 14, 2019 at 5:05 PM Laurenz Albe wrote: > Since you say that there was a regular data directory there, that would point > to permission problems. I'm not a systemd expert, but since we are _before_ starting the cluster, the checks should be run as superuser, so I don't see how a permi

Re: help understanding pgbench results

On Fri, Jul 12, 2019 at 12:04 PM Luca Ferrari wrote: > Since tables are unlogged, I was expecting no much difference in > setting checkpoint_completion_target, but I got (average results): > - checkpoint_completion_target = 0.1 ==> 755 tps > - checkpoint_completation_target =

Re: How to run a task continuously in the background

On Fri, Jul 12, 2019 at 7:06 AM Dirk Mika wrote: > > > > A cron job will only run once a minute, not wake up every second. > > > > I would like to avoid external programs if possible. In the current Oracle > environment, there are potentially multiple schemas on a server in which > processing ca

Re: help understanding pgbench results

On Mon, Jul 15, 2019 at 1:35 PM Fabio Pardi wrote: > unlogged tables are not written to WAL, therefore checkpoints do not fit into > the picture (unless something else is writing data..). That's my thought, and I was not expecting any big change in tps due to checkpoint_completion_target on unlo

after restore the size of the database is increased

Hi all, this should be trivial, but if I dump and restore the very same database the restored one is bigger than the original one. I did vacuumed the database foo, then dumped and restored into bar, and the latter, even when vacuumed, remains bigger then the original one. No other activity was runn

Re: after restore the size of the database is increased

On Mon, Jul 15, 2019 at 7:07 PM Adrian Klaver wrote: > What does \l+ show? The same as pg_size_pretty: foo=# \l+ List of databases Name| Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace |

Re: after restore the size of the database is increased

On Mon, Jul 15, 2019 at 7:21 PM Peter Geoghegan wrote: > Sometimes B-Tree indexes can be *larger* after a REINDEX (or after > they're recreated with a CREATE INDEX). It's not that common, but it > does happen. There isn't actually a very large size difference here, > so it seems worth comparing in

Re: How to run a task continuously in the background

On Tue, Jul 16, 2019 at 7:32 AM Dirk Mika wrote: > It's not really important that the job runs once a second, but that it starts > immediately when I want it to. > > If I start a job with pg_cron, it will not be executed until the next full > minute at the earliest. > > The processing of the dat

Re: help understanding pgbench results

I've done another set of tests, and effectively it seems that, with unlogged tables, the checkpoint_completion_target does not influence the final results. I've increased the test duration in order to include several checkpoints within each run. First of all, initialization of the database: % pgbe

Re: How to run a task continuously in the background

On Wed, Jul 17, 2019 at 9:38 AM Dirk Mika wrote: > That is basically still my main question. How do I start a background job > (e.g. a function) which waits by polling or LISTEN / NOTIFY for records in a > table to be processed. You will have a trigger that, once new tuples are created (or old

Re: Issue related with patitioned table:How can I quickly determine which child table my record is in,given a specific primary key value?

On Wed, Jul 17, 2019 at 9:19 AM 王旭 wrote: > I tried something like this: e.g., for symbol_id 6365, > SELECT (hashint2(6365::SMALLINT)% 10) shouldn't this be modulus 3 instead of 10? The problem is that record 6365 is not where you expected to be? As far as I know, there is no easy user-level wa

Re: Issue related with patitioned table:How can I quickly determine which child table my record is in,given a specific primary key value?

On Wed, Jul 17, 2019 at 11:41 AM James(王旭) wrote: > From these results I can tell the route to a table is not even related with > the mod function, right? > So It's hard for me to do any kind of guesses... Because it is the wrong function. According to \d+ on a child table and partbounds.c the f

Re: Change in db size

On Wed, Jul 17, 2019 at 7:33 PM Sonam Sharma wrote: > > I took the backup using pg_dump with gzip option and restored it with psql. It does not change the way you backed up, chances are when you restored it the database cropped table and index bloating as already mentioned. The other, remote chan

Re: Postgers 9.3 - ubuntu 16.04 - Are clogs entires automatically deleted?

On Thu, Jul 18, 2019 at 10:34 AM Cumer Cristiano wrote: > Today I wanted to dump a database but pg_dump is complaining about missing > clog files. > I’m quite sure that nobody has deleted the files and that my filesystem is > consistent. > > If I look in my clog directory I can see the files sta

Re: Rearchitecting for storage

On Thu, Jul 18, 2019 at 10:09 PM Matthew Pounsett wrote: > That would likely keep the extra storage requirements small, but still > non-zero. Presumably the upgrade would be unnecessary if it could be done > without rewriting files. Is there any rule of thumb for making sure one has > enough

Re: Rearchitecting for storage

On Fri, Jul 19, 2019 at 4:41 PM Matthew Pounsett wrote: > My current backup plan for this database is on-site replication, and a > monthly pg_dump from the standby to be copied off-site. Doing per-table > backups sounds like a great way to end up with an inconsistent backup, but > perhaps I mi

Re: pg_upgrade : 9.X to 11.X issue CentoOS 7.6

On Tue, Jul 23, 2019 at 3:56 PM Perumal Raj wrote: > could not connect to source postmaster started with the command: > "/usr/pgsql-9.2/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/data/db/data" > -o "-p 5432 -b -c listen_addresses='' -c unix_socket_permissions=0700 -c > unix_socket_directory

Re: Too slow to create new schema and their tables, functions, triggers.

On Fri, Jul 26, 2019 at 1:01 AM PegoraroF10 wrote: > > Nope, no one message near those statements. > I haven´t changed anything on Postgres.conf related with autovacuum. > Please take a look and post results of the following query: select name, setting from pg_settings where name like 'autovacuu

Re: Too slow to create new schema and their tables, functions, triggers.

On Fri, Jul 26, 2019 at 1:15 PM PegoraroF10 wrote: > > select count(*), count(*) filter (where last_autovacuum is not null) from > pg_stat_all_tables > count count > 36605 1178 > What are the results of the same query against pg_stat_sys_tables and pg_stat_user_tables? That's would help under

Re: Too slow to create new schema and their tables, functions, triggers.

On Fri, Jul 26, 2019 at 9:21 PM PegoraroF10 wrote: > So, is it better to configure autovacuum properly to these tables or should > I run vacuum periodically ? > Obviously I´ll check our script too. > My guess would be that either you have disabled autovacuum on such tables (I don't know if that i

Re: Error

On Mon, Jul 29, 2019 at 2:22 PM Sonam Sharma wrote: > Java.lang.illegalstateexception: 3 rows retrieved for single property server > is.Checksum. This has nothing to do with PostgreSQL, at least I suspect you have got a query that is not strict and is returning more than a row. You need to provi

Re: How do I create a Backup Operator account ?

On Tue, Jul 30, 2019 at 2:50 AM Marcos Aurelio Nobre wrote: > I was wondering if it was possible to configure the pgAdmin4 menus to be > available for a particular login, only Backup & Restore items. But I'm not > sure how to associate a bank login account with a specific menu setting of a > cl

Re: Rearchitecting for storage

On Thu, Jul 18, 2019 at 10:09 PM Matthew Pounsett wrote: > That would likely keep the extra storage requirements small, but still > non-zero. Presumably the upgrade would be unnecessary if it could be done > without rewriting files. Is there any rule of thumb for making sure one has > enough

Re: How do I create a Backup Operator account ?

On Tue, Jul 30, 2019 at 2:22 PM Ron wrote: > Luca, it is common some large Enterprise environments to have Operations > staff that can run backups without being able to do anything else. For > example, SQL Server has a per-database user mapping named db_backupoperator. Yes, but I think here we

Re: How do I create a Backup Operator account ?

On Wed, Jul 31, 2019 at 2:48 AM Marcos Aurelio Nobre wrote: > But I don't know how to implement this on Linux, nor how to write this entry > in the pg_hba.conf file. I would start with an entry in pg_hba.conf like the following: hostall pg_backup_usernamelocalhost

pgaudit.log_parameter

Hello, I'm a little confused about the setting pgaudit.log_parameter of the pgaudit extension (https://github.com/pgaudit/pgaudit/blob/master/README.md). What's the purpose of this? AN example of query that will trigger such parameter logging? Apparently I cannot get it providing me more informatio

Re: Which version to upgrade upto

On Wed, Jul 31, 2019 at 4:55 PM Vikas Sharma wrote: > Should I go for 10.9 or 11.2? The architects are suggesting 11.2 Moving fom 9.5 requires in any case a major version upgrade, therefore I would go for the latest one, 11.4. Are there any particular needs that feed your doubts about the version

Re: Cursors for PGJDBC queries

On Thu, Aug 1, 2019 at 9:10 AM Rashmi V Bharadwaj wrote: > I am trying to set the fetch size for my ResultSet to avoid Out of Memory > exception. I have created the Statement with ResultSet.TYPE_FORWARD_ONLY, > ResultSet.CONCUR_READ_ONLY and ResultSet.HOLD_CURSORS_OVER_COMMIT and I've > also di

Re: Cursors for PGJDBC queries

On Thu, Aug 1, 2019 at 9:30 AM Luca Ferrari wrote: > Hard to say without more information. Could it be something you need > to set on the jvm like sorry, I was intended to write -Xms256m -Xmx1024m to adjust the jvm memory limits. Again I believe that the jdbc mailing list is the right pl

Re: Altering multiple column types

On Fri, Aug 2, 2019 at 7:42 AM Bharanee Rathna wrote: > > Hi, > > I'm encountering an issue altering multiple column types in a single ALTER > TABLE > > psql (12beta2, server 11.4) it is working on my 11.4 with psql 11.4. Could it be a problem of psql version 12? Can you try again with a psql "s

Re: Altering multiple column types

On Fri, Aug 2, 2019 at 9:39 AM Bharanee Rathna wrote: > > Hi Luca, > > I've tried it with a different client and Postgres 10.9, no luck > > psql (10.3, server 10.9) I've fired up a 12beta2 and it works, either with psql 12 or psql 11.4 on linux. What if you run the statements within another clien

Re: Altering multiple column types

On Fri, Aug 2, 2019 at 10:06 AM Bharanee Rathna wrote: > > Hi Luca, > > testing this using docker images. I can replicate it with 10.9-alpine > > bash-5.0# psql -h127.0.0.1 -Upostgres test > psql (10.9) > Type "help" for help. > > test=# \d users > Table "public

Re: Does pgadmin4 work with postgresql 8.4?

On Tue, Aug 6, 2019 at 7:02 AM Murtuza Zabuawala wrote: > It is mentioned on the front page of https://www.pgadmin.org/ > (Check introduction texts on the elephant wallpaper) "pgAdmin may be used on Linux, Unix, Mac OS X and Windows to manage PostgreSQL 9.2 and above." However, I would suspect i

vacuum & free space map

Hi, I'm not understanding why a vacuum full clears a FSM information while a normal vacuum does not. On a table with fillfactor = 50 I've got: testdb=> vacuum full respi.pull_status; VACUUM testdb=> SELECT * FROM pg_freespace( 'respi.pull_status', 0 ); pg_freespace -- 0 (1

Re: Preventing in-session 'set role' commands

On Tue, Aug 6, 2019 at 10:26 AM VO Ipfix wrote: > > Is this something that can be accomplished with PostgreSQL? Any suggestions > thoughts are welcome, however tangential Perhaps SET SESSION AUTHORIZATION? Luca

Re: vacuum & free space map

On Tue, Aug 6, 2019 at 3:50 PM Tom Lane wrote: > VAC FULL builds a new physical table, which has no FSM to start with. Thanks, that was I was suspecting. Luca

Re: Does pgadmin4 work with postgresql 8.4?

On Tue, Aug 6, 2019 at 2:46 PM Benedict Holland wrote: > > To me, there is a huge difference between unsupported and wont work. ote: Thta' why the only truly working software you has is psql 8.4. As other has stated, it is too far in the past to say what is working and what not, you have to walk

Re: Why must AUTOCOMMIT be ON to do txn control in plpgsql procedure?

On Wed, Aug 7, 2019 at 12:19 AM Bryn Llewellyn wrote: > 1. my call p2() starts a txn. In my opinion this is the point: a procedure must be "owner" of the transaction to issue transaction control statements. You can watch different behaviors placing here and there txid_current() before and within

Re: pg_wal fills up on big update query

On Wed, Aug 7, 2019 at 3:34 PM Daniel Fink (PDF) wrote: > My current idea is to lock both tables completely from access (the queried > and the updated one) so that postgresql does not have to ensure isolation for > concurrent queries by keeping a copy of each row. I'm not sure that locking will

Re: Why must AUTOCOMMIT be ON to do txn control in plpgsql procedure?

On Wed, Aug 7, 2019 at 8:28 PM Bryn Llewellyn wrote: > About your “I believe there is no reason ever to begin a procedure with a > rollback”, I already explained why I did that. My higher goal is to take > advantage of the “serializable” isolation level to safely enforce a multi-row > data rule

Re: Why must AUTOCOMMIT be ON to do txn control in plpgsql procedure?

On Wed, Aug 7, 2019 at 11:36 PM Benedict Holland wrote: > > Also, I have never seen a rollback at the start of a proc. A pure > hypothetical is that it is doing nothing or definitely not what you think it > is. That's my point, thanks. Luca

Re: Having the issue in while selecting the data and feltering in order by.

On Thu, Aug 8, 2019 at 11:20 AM nikhil raj wrote: > Same when i run this in linux machine i am getting this out in different > sort order on the same query. A collation problem? What does this query do? SELECT * FROM (VALUES ('a'), ('___b1'), ('_a1'),('a2'),('a3'),('a5'), ('a2')) t (val) order

Re: Understanding PostgreSQL installer debug log

On Fri, Aug 9, 2019 at 8:46 AM Ramesh Maddi wrote: > ./postgresql-9.6.6-1-linux-x64.run --installer-language en --serviceaccount > postgres --servicename postgresqld --datadir "/home/postgres/" --prefix > "/home/postgres" --superpassword 1234 --serverport 5432 --debuglevel 4 > --debugtrace ./

Re: Understanding PostgreSQL installer debug log

On Fri, Aug 9, 2019 at 11:01 AM Ramesh Maddi wrote: > Thanks in advance and appreciate your response. Please consider this as a > high priority for us. I don't think re-sending the mes

Re: ...

On Fri, Aug 9, 2019 at 12:10 PM Daniel Vos wrote: > I use pgadmin 4.11 on windows 10 ( firefox, opera), > with a table more 3000 rows > if I use the contextual menu "view / edit data -> all rows", the grid > result show 1000 rows. Works fine for me on linux/firefox 68. How can you say not all tu

Re: Updating 3-table dataset

On Fri, Aug 9, 2019 at 2:29 PM Moreno Andreo wrote: > > Hi all, > I don't know if that's the heat burning my brain but I can't find a > solution to what seemed a simple operation to me. > > I have 3 tables > create table t_all > { > id uuid, > ref_id uuid (FK to t_ana.id) > }; > create ta

Re: ORA-24345: A Truncation or null fetch error occurred -ora2pg

On Tue, Aug 13, 2019 at 10:23 AM Daulat Ram wrote: > Initially did not have LongReadLen set, so I thought this was the cause. But, > I have set LongReadLen, on the db handle, equal to 9000. Apparently this is an oracle problem because it acceppted data longer than its type, so my guess would

Re: Changing work_mem

On Tue, Aug 13, 2019 at 5:59 PM rihad wrote: > [dbname] LOG: temporary file: path > "base/pgsql_tmp/pgsql_tmp93683.257381", size 594 > The setting 'work_mem' is within context 'user', that means it will affect running sessione unless the session itself has already issued a SET work_mem to xxx. S

Re: Question on pgwatch

On Wed, Aug 14, 2019 at 5:10 PM Bikram MAJUMDAR wrote: > From where did you download docker for linux, and , the docker with the > pgwatch container? > And, any installation/configuration tips for pgwatch running on linux? I'm not sure what you are effectively askin to me, however the starting

Re: Variable constants ?

On Thu, Aug 15, 2019 at 11:27 PM Rich Shepard wrote: > create table labor_rate_mult ( >rate real primary_key, >start_date date not null, >end_date date > ) I think the rate should not be the primary key, since that would prevent keeping the whole history when the

Re: Question on pgwatch

On Fri, Aug 16, 2019 at 11:57 PM Bikram MAJUMDAR wrote: > When I go to the URL link for pgwatch that you have given I see the following > : How do I get to pgwatch docker download and install on my linux server? I don't want to be harsh, but you should start doing your homework and report about

question about zeroes in the wal file names

I'm just curious to better understand the naming convention behind wal files, because I've seen on a system of mine that the wals created were: 0005020E00FF 0005020F while I was expecting 20E0x100. So I digged into the code and I've seen, from the XLogFileName macro,

Re: Rename a column if not already renamed.?

On Tue, Aug 20, 2019 at 9:07 PM Day, David wrote: > EXECUTE format ('ALTER TABLE %s RENAME %s TO %s', schema_table_, > old_name_, new_name); Hard to say without the error, but any chance there is a quoting problem? EXECUTE format ('ALTER TABLE %s RENAME %I TO %I', schema_table_, old_name_, n

Re: Permission for not Django app to do Write and Read

On Sat, Aug 24, 2019 at 11:53 AM Peter Wainaina wrote: > > Thanks much for the response. This is what I mean am a database administrator > for a production company and the product owner doesn't want me to be able to > either read or write information that will come from Django application. I pe

Re: For SELECT statement (just a reading one, no 'FOR UPDATE'), is COMMIT or ROLLBACK preferred?

On Sun, Aug 25, 2019 at 10:12 PM David Wall wrote: > The main issue is that if > we do a SELECT and get a ResultSet that has no rows, if we do a commit > or a rollback, it seems reasonable that these are identical as no > changes were made. My inclination is to do a Connection.commit() on the > c

Re: cannot CREATE INDEX because it has pending trigger events

On Tue, Aug 27, 2019 at 9:33 AM Simon Kissane wrote: > If I swap the order of the CREATE UNIQUE INDEX and the INSERT, so the index > gets created first, the error doesn't happen. It also works removing the INITIALLY DEFERRED from the foreign key, since it seems you are creating tuples in the rig

Re: psql \copy hanging

On Tue, Aug 27, 2019 at 9:54 AM Arnaud L. wrote: > Any other idea ? I'll change the lines order for tonight's run, but that > is not what I'd call a solution... Does it hangs against the same line content or the same line number? Are you able to run the script automatically during working hours (

Re: psql \copy hanging

On Tue, Aug 27, 2019 at 10:48 AM Arnaud L. wrote: > I can run the script just fine during working hours. I meant thru your scheduler (cron or something). > It hangs against the same line in the sql script, all lines being "\copy > (select ) to 'file on unc share'". This is a new detail to m

Re: psql \copy hanging

On Tue, Aug 27, 2019 at 12:34 PM Arnaud L. wrote: > I could do this but it would make the script a lot more complicated. > There are a lot of views that I \copy directly to this share, and this > is the only one that poses any problem. I would however give it a try. I would schedule a nightly exe

Re: Pointers towards identifying bulk import bottleneck (walwriter tuning?)

On Tue, Aug 27, 2019 at 12:06 PM Holtgrewe, Manuel wrote: > iotop tells me that walwriter does not go beyond ~35MB/s so maybe this is the > culprit? Is there a way to tune walwriter I/O performance? As far as I know, walwriter is there to help background processes, so in the case it cannot keep

Re: psql \copy hanging

On Wed, Aug 28, 2019 at 9:09 AM Arnaud L. wrote: > OK, so this was enough for last night's schedule to run without problem. > I still don't get it so I'm not satisfied with this solution, but at > least it works. > I'll keep the list informed if something new arises. I don't want to be pedantic,

Re: Work hours?

On Wed, Aug 28, 2019 at 12:27 AM stan wrote: > Any thoughts as to the best way to approach this? I've written a couple of functions to compute working hours depending on a possible per-day hour template. Another possible implementation besides the other proposed solutions.

Re: Question about password character in ECPG's connection string

On Wed, Aug 28, 2019 at 10:47 PM Alban Hertroys wrote: > Perhaps it helps to URL-encode the & in the password as %26? The OP already did without success. Could it be needed to escape the & with the backslash or single ticks? Luca

Re: Question about password character in ECPG's connection string

On Thu, Aug 29, 2019 at 1:08 PM Egashira, Yusuke wrote: > According to my tests, I think that the ECPG's connection_option seems not to > accept '&' character as password anyhow... > ECPG CONNECT's connection_option seems to have some restricted characters. As far as I understand from

Re: Security patch older releases

On Thu, Aug 29, 2019 at 2:05 PM Erika Knihti-Van Driessche wrote: > So, I have postgres 9.6.9 and customer wants it to be updated to 9.6.11, > because that is their current testing "sandbox" version, which was not > installed by me and is also another linux distro. Binary packages are always at

Re: implicit transaction changes trigger behaviour

On Thu, Aug 29, 2019 at 2:16 PM Willy-Bas Loos wrote: > delete from b; > --DELETE 3 Here the trigger is fired 3 times (for each row), and on every single test it finds a row in 'a', that is your variable n_b_type1 is always 1, that causes the trigger (fired on each row) to not abort. If you delet

literal vs dynamic partition constraint in plan execution

Ok, the title is a little buzz, however I've got a partitioned table and one "leaf" has a set of checks against a timestamp field to ensure that tuples within such table belongs to the year and month: testdb=# \d respi.y2019m08 ... Partition of: respi.y2019 FOR VALUES IN ('8') Check constraints:

Re: literal vs dynamic partition constraint in plan execution

On Thu, Aug 29, 2019 at 4:45 PM Luca Ferrari wrote: > > Ok, the title is a little buzz, however I've got a partitioned table > and one "leaf" has a set of checks against a timestamp field to ensure > that tuples within such table belongs to the year and month: Of co

Re: literal vs dynamic partition constraint in plan execution

On Fri, Aug 30, 2019 at 8:29 AM Luca Ferrari wrote: > testdb=># \d+ respi.y2018 > ... > Partition of: respi.root FOR VALUES IN ('2018') > Partition constraint: ((date_part('year'::text, mis_ora) IS NOT NULL) > AND (date_part('year'::text, mis_o

Re: Regarding db dump with Fc taking very long time to completion

On Fri, Aug 30, 2019 at 11:51 AM Durgamahesh Manne wrote: > Logical dump of that table is taking more than 7 hours to be completed > > I need to reduce to dump time of that table that has 88GB in size Good luck! I would see two possible solutions to the problem: 1) use physical backup and switc

Re: "storing" a calculated value in plsql function ?

On Fri, Aug 30, 2019 at 12:48 PM stan wrote: > In the resultant table, I have raw data, and adjusted data. The adjusted data > i > all adjusted by a common factor, which is calculated in the select. > Presently, I > calculate this same adjustment factor several times in the select. Is it possib

Re: How to get RAISE messges displayed?

On Sat, Aug 31, 2019 at 12:35 AM stan wrote: > Got it working. > > Not 100% sure what I had wrong. You can also do, in your session: set client_min_messages to notice; without having to change it in the configuration (for all sessions). Luca

partition by range or by list constraint check (was Re: literal vs dynamic partition constraint in plan execution)

I've done a simple test case, and find out that probably the problem I got was due to the partition schema I'm using. I want a table to be partitioned by a timestamp field with a first level partition by year, and a second level by month. Therefore, I did a BY LIST partitioning, but that produces a

Re: restore and recovery using WAL: unkown messages in serverlog

On Tue, Sep 3, 2019 at 9:57 AM Matthias Apitz wrote: > 2019-09-03 09:18:46.024 CEST [25388] LOG: Logdatei > »00010001008F« aus Archiv wiederhergestellt > ***> cp: der Aufruf von stat für > '/data/postgresql11/wal_archive/000100010090' ist nicht möglich: > Datei ode

Re: Postgres HA issue - Standby server does not start after Master compute host is shut down

On Tue, Sep 3, 2019 at 6:08 PM Nagendra Bandi wrote: > Standby is built from the primary using pg_basebackup. i.e. Initial copy of > the primary database is taken with pg_basebackup command and then restarted > the server. This puzzles me: < 2019-09-01 15:43:56.440 UTC >LOG: trigger file foun

Re: PG11.2 - wal_level =minimal max_wal_senders = 0

On Wed, Sep 4, 2019 at 5:15 PM Jason Ralph wrote: > The question I have is, for a production database not doing replication, can > I safely set the following parameters, I understand that minimal will also > disable wal_archiving so I am concerned about that as well. > > wal_level = minimal > >

Re: PG11.2 - wal_level =minimal max_wal_senders = 0

On Wed, Sep 4, 2019 at 10:44 PM Jason Ralph wrote: > > Thank you Luca, > Can I ask one more related question, I have no need for replication as noted > below, can I safely disable the worker process via the setting below? In my > sandbox it does turn off the logical replication launcher, I just

Re: floating point output

On Thu, Sep 5, 2019 at 6:14 AM Adrian Klaver wrote: > Some examples would help explain your concerns. I guess the problem is with extra_float_digits. If greater then zero, the string representation of a real is at its minimum, that is only significative digits are there: restoredb=# set extra_fl

Re: PG11.2 - wal_level =minimal max_wal_senders = 0

On Sat, Sep 7, 2019 at 2:15 AM Jason Ralph wrote: > I currently have the systems running a parallel pg_dump each night to a > separate partition mounted on the VM. Then I perform a full backup of the VM > and all mounted drives each night. Would this be affected by disabling wal > archiving? I

Re: Recover data from aborted transactions

On Tue, Sep 10, 2019 at 7:18 PM Aaron Spike wrote: > I'm pretty sure that the records I'm looking for are part of one of these > aborted transactions. From what I read online, it seems that data from > uncommitted transactions exists in the Write-Ahead Logs. Is there anyway to > access this dat

Re: update returning order by syntax error question

On Thu, Sep 12, 2019 at 5:45 AM raf wrote: > ERROR: syntax error at or near "t" > LINE 2: tblname t This works on 9.6.9 and 11.4: luca=> with u as ( update t_all set id = 5 where id <= 5 returning *) select * from u; id | ref_id + 5 | 1 (1 row) luca=> select version();

Re: pgbackrest restore to new location?

On Tue, Sep 17, 2019 at 3:09 AM Ron wrote: > > Hi, > > In order to do this, do I create a new stanza in config file which has > pg1-path point to the new/empty directory structure while still pointing to > the existing backup directory, and restore that stanza? No, I would do this: 1) execute sto

Re: pgbackrest restore to new location?

On Tue, Sep 17, 2019 at 12:00 PM Ron wrote: > The real problem is that after doing that, "pg_ctl start -D > /path/to/new/data" fails with "PANIC: could not locate a valid checkpoint > record". Hard to say what's going wrong without logs/configs. Do you have any other backup to try to restore from

Re: install pgcrypto module to existing postgreSQL

On Tue, Sep 17, 2019 at 11:19 PM Pavan Kumar wrote: > once configure is done, used make and make install to install postgres. cd contrib && make && make install that should work. Luca

unable to drop index because it does not exists

Hi, running 11.5 I've got a partitioned table where I want to destroy an index (on a column that has nothing to do with the partition): respidb=# \d+ respi.root Column|Type | --+-+- sen_id | integer

Re: pg_receivexlog or archive_command

On Mon, Sep 23, 2019 at 10:55 AM Andreas Kretschmer wrote: > you can use both of them, and you should consider "Barman". If I remember well Barman uses pg_receivexlog when streaming, and archive_command when doing a "normal" backup. Also pgbackrest is another good tool for backup. The idea here s

  1   2   3   >