Re: Issues with using plpgsql debugger using PG13 on Centos 7

2021年2月2日(火) 12:06 Jain, Ankit : (...) > But got the following error – > > ERROR: could not load library "/usr/pgsql-13/lib/plugin_debugger.so": > /usr/pgsql-13/lib/plugin_debugger.so: undefined symbol: LWLockAssign SQL > state: 58P01 > > > > Can you please help with getting the debugger working ?

Issues with using plpgsql debugger using PG13 on Centos 7

Hi, I am trying to setup the debugger plugin on Centos 7 where PG 13 server has been setup using the git repository located here - https://git.postgresql.org/gitweb/?p=pldebugger.git;a=summary The OS version is CentOS Linux release 7.9.2009 (Core). However, after making the changes to postgresq

Re: libpq and mysterious "invalid byte sequence for encoding UTF8".

rob stone writes: > Columns:- maincontact boolean DEFAULT false, > publdatacontact boolean DEFAULT false, > invcontact boolean DEFAULT false, > queries_recipient boolean, > fakelastname boolean NOT NULL DEFAULT false, > are defined as booleans and all except one ha

Re: count(*) vs count(id)

On Mon, Feb 1, 2021 at 7:19 PM Guillaume Lelarge wrote: > > > You're doing a left join, so I guess there's no row where > call_records.timestamp::date = 2020-08-30. That would result with a NULL id. > > Thanks for the excellent analysis everyone. I appreciate it! Here is the documentation (for a

Re: libpq and mysterious "invalid byte sequence for encoding UTF8".

Hello, On Mon, 2021-02-01 at 18:03 +0100, Jiří Pavlovský wrote: > > > Thanks for the answer. > > > I don't think triggers or constraints are the issue. > > The exact same code works if I create a small test program with the > same > query and the same parameters. > > But when used in the co

Re: ransomware

On Mon, Feb 01, 2021 at 03:38:35PM +0100, Marc Millas wrote: > there are various ways to do those checks but I was wandering if any > ""standard''" solution exist within postgres ecosystem, or someone do have > any feedback on the topic. It seems to me that you should first write down on a sheet o

Re: count(*) vs count(id)

Le mar. 2 févr. 2021 à 02:14, Matt Zagrabelny a écrit : > > > On Mon, Feb 1, 2021 at 6:35 PM Tom Lane wrote: > >> Matt Zagrabelny writes: >> > On Mon, Feb 1, 2021 at 5:57 PM Rob Sargent >> wrote: >> >> You got one null from count(*) likely. >> >> > What is count(*) counting then? I thought it

Re: count(*) vs count(id)

At 2021-02-01T20:14:04-05:00, Matt Zagrabelny sent: > select count(id) from call_records where id is null; > count > ═══ > 0 > (1 row) > > Time: 0.673 ms > > Which field is count(*) counting if it is counting nulls? > > -m What you're overlooking is that, at least to my reading of y

Re: count(*) vs count(id)

On Mon, Feb 1, 2021 at 6:14 PM Matt Zagrabelny wrote: > Which field is count(*) counting if it is counting nulls? > count(id) excludes from the count rows where the id field is null. The presence of a left join in your query is introducing a null here due to there not being a related field, not

Re: count(*) vs count(id)

On Mon, Feb 1, 2021 at 6:35 PM Tom Lane wrote: > Matt Zagrabelny writes: > > On Mon, Feb 1, 2021 at 5:57 PM Rob Sargent > wrote: > >> You got one null from count(*) likely. > > > What is count(*) counting then? I thought it was rows. > > Yeah, but count(id) only counts rows where id isn't null.

Re: count(*) vs count(id)

Matt Zagrabelny writes: > On Mon, Feb 1, 2021 at 5:57 PM Rob Sargent wrote: >> You got one null from count(*) likely. > What is count(*) counting then? I thought it was rows. Yeah, but count(id) only counts rows where id isn't null. regards, tom lane

Re: count(*) vs count(id)

On Mon, Feb 1, 2021 at 5:57 PM Rob Sargent wrote: [...] > > You got one null from count(*) likely. > What is count(*) counting then? I thought it was rows. -m

Re: count(*) vs count(id)

On 2/1/21 4:53 PM, Matt Zagrabelny wrote: Greetings, Is there a preferred method of counting rows? count(*) vs count(field_name) I have used count(*) for a long time and am hitting an inconsistency. At least it is an inconsistency to me (I'm sure there is an explanation)... INCORRECT OUT

count(*) vs count(id)

Greetings, Is there a preferred method of counting rows? count(*) vs count(field_name) I have used count(*) for a long time and am hitting an inconsistency. At least it is an inconsistency to me (I'm sure there is an explanation)... INCORRECT OUTPUT (not desired results) $ select calendar.entry

Re: permission denied for large object 200936761

On 2/1/21 3:07 PM, Andrus wrote: Hi! > What code changed between the last backup and today? I have imported data from other clusters and executed lot of different sql commands.  I have used grant, revoke, reassign commands to change privileges for other users and have deleted and added users

Re: permission denied for large object 200936761

Hi! > What code changed between the last backup and today? I have imported data from other clusters and executed lot of different sql commands.  I have used grant, revoke, reassign commands to change privileges for other users and have deleted and added users. Cluster contains 25 databases.

Re: permission denied for large object 200936761

On 2/1/21 2:20 PM, Andrus wrote: Hi! >Obviously large objects *are* used. >You have to grant the database use permissions with > GRANT SELECT ON LARGE OBJECT 200936761 TO dumpuser; >Alternatively, use the -B option of pg_dump to skip dumping >large objects. I added -B option and changed

Re: permission denied for large object 200936761

Hi! >Obviously large objects *are* used. >You have to grant the database use permissions with > GRANT SELECT ON LARGE OBJECT 200936761 TO dumpuser; >Alternatively, use the -B option of pg_dump to skip dumping >large objects. I added -B option and changed postgresql.conf  to lo_compat_priv

Re: vacuum is time consuming

Hi Atul, > We have DB of 400GBs, Server RAM is 160GBs. CPU consumption is 1-3% only. > > We have configured maintenance_work_mem to 10GBs and restarted the > postgres service. Just wanted to mention that maintenance_work_mem has a hardcoded upper limit threshold of 1GB, so any size bigger than

Re: permission denied for large object 200936761

>I misspoke earlier about large objects not being tied to a schema.table. They can be as a column of type oid. To see if they are try : SELECT     relname,     attname FROM     pg_attribute AS pa     JOIN pg_class AS pc ON pa.attrelid = pc.oid WHERE     atttypid = 'oid'::regtype     AND relname

Re: permission denied for large object 200936761

Hi! >Long term figure out what they are and if they are needed or not. Non-superuser backup worked earlier. It looks like large objects suddenly appeared in database: select * from  pg_largeobject_metadata Oid  Lomowner 200936761  30152 200936762  30152 20093676

Re: permission denied for large object 200936761

On 2/1/21 1:28 PM, Andrus wrote: Hi! >Long term figure out what they are and if they are needed or not. Non-superuser backup worked earlier. It looks like large objects suddenly appeared in database: select * from  pg_largeobject_metadata Oid  Lomowner 200936761 

Re: permission denied for large object 200936761

On 2/1/21 12:07 PM, Andrus wrote: Hi!  >Large objects exist independent of those. The important part of the above is lomowner. Use that oid to find the role that owns the objects here: /select rolname from pg_roles where oid = 30152 ; // Not sure what the above is supposed to be doing?

Re: permission denied for large object 200936761

Hi!  >Large objects exist independent of those. The important part of the above is lomowner. Use that oid to find the role that owns the objects here: /select rolname from pg_roles where oid = 30152 ; // Not sure what the above is supposed to be doing? I showed the user definition I

Re: permission denied for large object 200936761

On Mon, 2021-02-01 at 18:32 +0200, Andrus wrote: > > > Database does not contain large objects. > > > > > > pg_dump starts to throw error > > > > > > ERROR: permission denied for large object 200936761 > > > > Did you do the pg_dump as a superuser? > > No. > > pg_dump needs to be invoke

Re: Segmentation fault on startup

Helmut Bender writes: > I'm running a nextcloud server in a docker container on an RasPi 4 (only > SSD, no SD), which uses PostgreSQL 10 as server. 10.what? We're already up to 15 patch releases for that branch. > Today I had to restart the RasPi. Now the container fails to start with > a seg

Re: permission denied for large object 200936761

On 2/1/21 9:55 AM, Andrus wrote: Hi! >Large objects exist independent of those. The important part of the above is lomowner. Use that oid to find the role that owns the objects here: /select rolname from pg_roles where oid = 30152 ; // / returns my role ,  clusteradmin . I have superuser r

Re: permission denied for large object 200936761

Hi! >Large objects exist independent of those. The important part of the above is lomowner. Use that oid to find the role that owns the objects here: /select rolname from pg_roles where oid = 30152 ; // / returns my role ,  clusteradmin . I have superuser rights: CREATE ROLE clusteradmin WI

Re: permission denied for large object 200936761

Hi! >Well the user that runs the pg_dump needs to have permissions on the large objects. For more information see below. How to add permissions to non-superusers for this.? GRANT command GRANT { { SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] } ON LARGE OBJECT/|loid|/ [, ...] TO/|r

Re: permission denied for large object 200936761

Hi! Database does not contain large objects. pg_dump starts to throw error ERROR:  permission denied for large object 200936761 Did you do the pg_dump as a superuser? No. pg_dump needs to be invoked by non-superuser also. It backs up two schemas, public and firma74 . -n public -n firma

Segmentation fault on startup

Hi, I'm running a nextcloud server in a docker container on an RasPi 4 (only SSD, no SD), which uses PostgreSQL 10 as server. The containers are started via docker compose. The PostgreSQL service looks like services: db: image: postgres:10-alpine restar

Re: permission denied for large object 200936761

On 2/1/21 9:13 AM, Andrus wrote: Hi! >Well the user that runs the pg_dump needs to have permissions on the large objects. For more information see below. How to add permissions to non-superusers for this.? GRANT command GRANT { { SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] } ON LAR

Re: libpq and mysterious "invalid byte sequence for encoding UTF8".

On 01.02.2021 15:55, Tom Lane wrote: =?UTF-8?B?SmnFmcOtIFBhdmxvdnNrw70=?= writes:     Jan 26 08:40:57 ip-172-16-10-94 postgres[11334]: [113-1] 2021-01-26 09:40:57.505 CET [11334] jira@project-syndicate LOG:  execute :     UPDATE contacts SET Pos = $1::varchar, Title = $2::varchar,

Re: permission denied for large object 200936761

On 2/1/21 8:32 AM, Andrus wrote: Hi! Database does not contain large objects. pg_dump starts to throw error ERROR:  permission denied for large object 200936761 Did you do the pg_dump as a superuser? No. pg_dump needs to be invoked by non-superuser also. It backs up two schemas, public

Re: permission denied for large object 200936761

On 2/1/21 6:43 AM, Andrus wrote: Hi! Database does not contain large objects. pg_dump starts to throw error ERROR:  permission denied for large object 200936761 Did you do the pg_dump as a superuser? More below. Tried select * from "200936761" but it returned "relation does not exist"

permission denied for large object 200936761

Hi! Database does not contain large objects. pg_dump starts to throw error ERROR:  permission denied for large object 200936761 Tried select * from "200936761" but it returned "relation does not exist" How to fix this ? How to find which table causes this error ? How to find and delete all

Re: libpq and mysterious "invalid byte sequence for encoding UTF8".

=?UTF-8?B?SmnFmcOtIFBhdmxvdnNrw70=?= writes: >     Jan 26 08:40:57 ip-172-16-10-94 postgres[11334]: [113-1] 2021-01-26 > 09:40:57.505 CET [11334] jira@project-syndicate LOG:  execute : >     >     UPDATE contacts SET Pos = $1::varchar, Title = $2::varchar, > PaperID = $3::int, FirstName = $4::

ransomware

Hi, I have been asked the following question: is there anyway, from within postgres, to detect any ""abnormal"" disk writing activity ? obvious goal would be to alert if... its quite clear that the underlying OS is the place to do the checks, but, still --to my understanding, a simple script can

Re: Edb Jdbc Ssl Connection

Please follow the instructions here Configuring the Client (postgresql.org) Dave Cramer www.postgres.rocks On Mon, 1 Feb 2021 at 01:44, wrote: > > 42.2.9 and java 8

libpq and mysterious "invalid byte sequence for encoding UTF8".

I'm stuck trying to find a cause for > invalid byte sequence for encoding "UTF8". It is an C program using libpq. I'm using `PQexecParams` to execute the SQL query. The offending byte sequence is completely random, sometimes the command even runs ok. I thought I must have a memory allocation iss

SV: SV: Npgsql and the Connection Service File

>On Sat, 2021-01-30 at 15:56 +, Niels Jespersen wrote: >> It would be nice if Npgsql (and jdbc and others) emulated the libpq >> behaviour. >> Because in my mind, abstracting hostname, portnumber and databasename >> away is a really useful feature. >> >> How do others manage this? > >Eithe

Re: SV: Npgsql and the Connection Service File

On Sat, 2021-01-30 at 15:56 +, Niels Jespersen wrote: > It would be nice if Npgsql (and jdbc and others) emulated the libpq behaviour. > Because in my mind, abstracting hostname, portnumber and databasename > away is a really useful feature. > > How do others manage this? Either they don'