Re: PG11 Hash partitioning and null values in the partition key

[Daniel Westermann]

Hi, 

given this setup: 

create table part2 ( a int, list varchar(10) ) partition by hash (a); 
create table part2_1 partition of part2 FOR VALUES WITH (MODULUS 3, REMAINDER 
0); 
create table part2_2 partition of part2 FOR VALUES WITH (MODULUS 3, REMAINDER 
1); 
create table part2_3 partition of part2 FOR VALUES WITH (MODULUS 3, REMAINDER 
2); 

insert into part2 (a,list) values (1,'aaa'); 
insert into part2 (a,list) values (2,'bbb'); 
insert into part2 (a,list) values (3,'ccc'); 

... it is possible to insert rows like this which will always go to the first 
partition: 

insert into part2 (a,list) values (null,'ddd'); 
insert into part2 (a,list) values (null,'eee'); 
select * from part2_1; 
a | list 
---+-- 
2 | bbb 
| ddd 
| eee 
(3 rows) 

I suppose this is intended but I could not find anything in the documentation 
about that. Can someone please clarify the logic behind that? 



>> The calculated hash value for the null value will be zero, therefore, it 
>> will fall to the partition having remainder zero. 


Thank you, Amul 

Re: ODBC - Getting CONN ERROR: errmsg='The buffer was too small for the InfoValue'

[Edgard Battisti Guimarães]

Ok, Done!

thanks for you help

Em sex, 13 de jul de 2018 às 10:29, Adrian Klaver 
escreveu:

> On 07/12/2018 04:56 PM, Edgard Battisti Guimarães wrote:
> > --- The ODBC Global tracing was turned on too but any one entry was made
> > from the powerbuilder app.
> >
> > --- No problem on sending the entire PB app, but it's really big, so
> > follows the initial script with the connect and the ini file content
> > with the parameters. I will make a minimalist PB script to help with the
> > problem isolation.
> >
> > ---  The ODBC driverusedin all caseswas the 32 bit .
> >
> > --- The original computer where the program is working ok is a DELL
> > laptop inspiron 7548 core i5  8GB
> >
> > --- The computer presenting the reported problem is a Lenovo Yoga laptop
> > 720-121KB core i5 8GB
> >
> > --- The locale: The original and third computer are the same - The OS
> > local is Portugues(Brasil), the language, date format, number format etc
> > are the same: DD/MM/.  Numbers use comma as decimal point and dot as
> > group separator.
> >
> > --- By running the script below, I receive a messagebox with the
> > following content:
> > ==
> > erro -1 na abertura do BD
> >
> > SQLSTATE = IM001
> > [Microsoft]ODBC Driver Manager] Driver does not support this function
> > ==
>
> My suggestion at this point would be to ask for advice on the -odbc list:
>
> https://www.postgresql.org/list/pgsql-odbc/
>
>
> >
> >
> >
> > The PB script:
> > ===
> > string s, s1, aux, aux1
> > int qtc1, qtc2, retornoprofiles, atudbparms
> > string chlic, chcalc, testa_nr_carros
> > date dataref
> > int i
> >
> >
> > s = "ONIBUS.INI"
> >
> > sqlca.DBMS   = ProfileString (s, "sqlca", "dbms",   "")
> > sqlca.dbparm = ProfileString (s, "sqlca", "dbparm", "")
> >
> >
> > CONNECT using sqlca;
> >
> > if sqlca.sqlcode <> 0 then
> > messagebox ("erro " + string(sqlca.sqlcode) + " na abertura do BD", &
> > sqlca.sqlerrtext)
> > if messagebox("ÔNIBUS","Se voce tem um Banco de Dados salvo~n"+&
> > "pode usar a opção Arquivo/Retornar BD.~n~n"+&
> > "Voce deseja prosseguir?",Question!, Yesno!) = 2 then
> > return
> > end if
> > end if
> >
> >
> >
> >
> > The ONIBUS.INI parameters file
> > =
> > [sqlca]
> > dbms=ODBC
> > DbParm=ConnectString='Driver={Postgresql
> > Unicode};Server=localhost;Port=5432;Database=onbpdc;Uid=dba;Pwd=sql';
> > ==
> >
> >
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>


-- 
Edgard Battisti Guimarães

Re: ERROR: found multixact from before relminmxid

[Alvaro Herrera]

On 2018-Jul-13, Sean McIntyre wrote:

> Quick correction :)
> 
> I am running PostgreSQL *9.6.8* on AWS RDS. I'm going to try to pg-repack
> the table and will look to upgrade to *9.6.9* (though it's not presently
> available on RDS).

Yay RDS ...

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: ERROR: found multixact from before relminmxid

[Sean McIntyre]

Quick correction :)

I am running PostgreSQL *9.6.8* on AWS RDS. I'm going to try to pg-repack
the table and will look to upgrade to *9.6.9* (though it's not presently
available on RDS).

Sean

On Fri, Jul 13, 2018 at 11:14 AM Sean McIntyre <
sean.mcint...@warbyparker.com> wrote:

> Dear all,
>
> I just wanted to report that I found similar error "found multixact from
> before relminmxid" that Alexandre Arruda and Jeremy Finzel recently
> reported and discussed with Andres Freund. (Thanks all for good notes.)
>
> I get this error when vacuuming a specific table in my high-transaction
> OLTP workload database. I am running PostgreSQL 9.5.8 on AWS RDS. I'm going
> to try to pg-repack the table and will look to upgrade to 9.5.9 (though
> it's not presently available).
>
> Best,
>
> Sean
>
> --
>
> Sean McIntyre
> WARBY PARKER
> 
>
> Summer reading calls for summery frames
> 
> We made an app! Download it here
> 
> Follow us:  Facebook
> 
> , Twitter
> 
> , Instagram , Snapchat
> 
>


-- 

Sean McIntyre
WARBY PARKER


Summer reading calls for summery frames

We made an app! Download it here

Follow us:  Facebook

, Twitter

, Instagram , Snapchat

Re: PG11 Hash partitioning and null values in the partition key

[amul sul]

On Fri, Jul 13, 2018, 7:35 PM Daniel Westermann <
daniel.westerm...@dbi-services.com> wrote:

> Hi,
>
> given this setup:
>
> create table part2 ( a int, list varchar(10) ) partition by hash (a);
> create table part2_1 partition of part2 FOR VALUES WITH (MODULUS 3,
> REMAINDER 0);
> create table part2_2 partition of part2 FOR VALUES WITH (MODULUS 3,
> REMAINDER 1);
> create table part2_3 partition of part2 FOR VALUES WITH (MODULUS 3,
> REMAINDER 2);
>
> insert into part2 (a,list) values (1,'aaa');
> insert into part2 (a,list) values (2,'bbb');
> insert into part2 (a,list) values (3,'ccc');
>
> ... it is possible to insert rows like this which will always go to the
> first partition:
>
> insert into part2 (a,list) values (null,'ddd');
> insert into part2 (a,list) values (null,'eee');
> select * from part2_1;
>  a | list
> ---+--
>  2 | bbb
>| ddd
>| eee
> (3 rows)
>
> I suppose this is intended but I could not find anything in the
> documentation about that. Can someone please clarify the logic behind that?
>

The calculated hash value for the null value will be zero, therefore, it
will fall to the partition having remainder zero.

​Regards,
Amul​

Re: Read only to schema

[Łukasz Jarych]

I found something like this:

CREATE ROLE readonly_user
   WITH LOGIN
   ENCRYPTED PASSWORD '1234'

ALTER ROLE readonly_user
SET search_path to
public

 GRANT CONNECT
ON DATABASE "TestDb"
TO readonly_user;
 GRANT USAGE
ON SCHEMA public
TO readonly_user;
GRANT USAGE
ON ALL SEQUENCES  -- Alternatively: ON SEQUENCE seq1, seq2, seq3 ...
IN SCHEMA public
TO readonly_user;
 GRANT SELECT
ON ALL TABLES  -- Alternatively: ON TABLE table1, view1, table2 ...
IN SCHEMA public
TO readonly_user;

Question is how to give this user opposite access? I mean give him access
to all functionalities like inserting, deleting, creating tables and staff
like this.

I mean i want to assign user "jaryszek" to this read_only role and after
changing schema i want to give user "jaryszek" all credentials.

Best,
Jacek





pt., 13 lip 2018 o 12:58 Łukasz Jarych  napisał(a):

> Maybe read-only view?
>
> Best,
> Jacek
>
> pt., 13 lip 2018 o 07:00 Łukasz Jarych  napisał(a):
>
>> Hi Guys,
>>
>> Yesterday i tried all day to figure out system to read only schemas.
>>
>> I want to :
>>
>> 1. Create user who can login (user: jaryszek)
>> 2. Create role who can read only data (only watching tables) (role:
>> readonly)
>> 3, Create role who can read all data (inserting, deleting, altering,
>> dropping) (role: readall)
>>
>> What sqls should i use for this?
>> What grants should i add?
>>
>> And now i am logged as jaryszek
>>
>> I want to grant myself role read only to schema public (when owner is
>> postgres).
>> I want to review tables as views only,
>> After work i want to grant myself role readall to schema public.
>>
>> It is possible?
>> Or possible workaround ?
>>
>> Best,
>> Jacek
>>
>

RE: Monitor repl slot size

[Igor Neyman]

From: Nicola Contu [mailto:nicola.co...@gmail.com]
Sent: Friday, July 13, 2018 6:19 AM
To: pgsql-general@lists.postgresql.org
Cc: Alessandro Aste 
Subject: Monitor repl slot size

Hello,
we used to monitor the replication slot size on postgres 9.6.6 with the 
following query:

SELECT pg_xlog_location_diff(pg_current_xlog_location(), restart_lsn) FROM 
pg_replication_slots WHERE slot_name = 'SLOT NAME';

We are moving to postgres 10.4 and we saw the pg_xlog_location_diff is not 
there anymore.


I know we can re-create it following this link : 
https://github.com/DataDog/integrations-core/issues/907


but, is there any better way to do it? Any replacement for that function on 
postgres 10?


Thanks a lot,
Nicola

I’m compare current_wal_lsn to confirmed_flush_lsn:

SELECT confirmed_flush_lsn, pg_current_wal_lsn(), (pg_current_wal_lsn() - 
confirmed_flush_lsn) AS lsn_distance  -- returned as NUMERIC
   FROM pg_catalog.pg_replication_slots
   WHERE slot_name = 'Slot NAME';

Regards,
Igor Neyman

AW: Disable TRUST authentication by using ClientAuthentication_hook

[kpi6288]

> -Ursprüngliche Nachricht-
> Von: Tom Lane 
> 
> > If you're an server admin you can disable the extension (editing
> > shared_pre_load_libraries GUC), change password and then enable the
> > extension again...

I am aware of this and all the other points. 

> Or more to the point: exactly what is the threat model here?  

It is similar like with your garage door: locking it with a simple 50 
year-old-key is still better than just clamping it with a wedge. It is 
certainly not as good as enforcing the door and putting a modern and solid lock 
to it. 

> ISTM that
> someone with enough privilege to alter pg_hba.conf can probably suppress
> loading of an extension too, so that the security added by this idea is not 
> just
> questionable but completely illusory.

This is a valid point of concern. However, settings in pg_hba.conf need to be 
documented to allow modification of IP address ranges etc. A few people have 
access to this and it is likely that they look into the manuals and find 
alternative settings. Configuration of libraries is not clear to everyone. 

> 
> What would actually move the goalposts a bit is to build a modified server
> which doesn't have the TRUST code path at all, so that there is no question of
> installing an extension or not; then somebody who wants to defeat the
> security needs to be able to replace the server executable.  But you don't
> need any hook if you do that.

That is true but I came across a discussion that for several reasons a proposal 
to add build-time options for authentication methods was not implemented. I'm 
trying to avoid modification of the source code if I can. I agree that I may 
have to build a modified server if I don't find a better solution. 

Regards Klaus

PG11 Hash partitioning and null values in the partition key

[Daniel Westermann]

Hi, 

given this setup: 

create table part2 ( a int, list varchar(10) ) partition by hash (a); 
create table part2_1 partition of part2 FOR VALUES WITH (MODULUS 3, REMAINDER 
0); 
create table part2_2 partition of part2 FOR VALUES WITH (MODULUS 3, REMAINDER 
1); 
create table part2_3 partition of part2 FOR VALUES WITH (MODULUS 3, REMAINDER 
2); 

insert into part2 (a,list) values (1,'aaa'); 
insert into part2 (a,list) values (2,'bbb'); 
insert into part2 (a,list) values (3,'ccc'); 

... it is possible to insert rows like this which will always go to the first 
partition: 

insert into part2 (a,list) values (null,'ddd'); 
insert into part2 (a,list) values (null,'eee'); 
select * from part2_1; 
a | list 
---+-- 
2 | bbb 
| ddd 
| eee 
(3 rows) 

I suppose this is intended but I could not find anything in the documentation 
about that. Can someone please clarify the logic behind that? 

Thanks in advance 
Daniel 

Re: Handlind booleans Postgresql-Access

[Łukasz Jarych]

Hmm so maybe better is use text field here

Best,
Jacek

pt., 13 lip 2018 o 15:36 Adrian Klaver 
napisał(a):

> On 07/13/2018 02:19 AM, Łukasz Jarych wrote:
> > Hi Guys,
> >
> > i created boolean in postgresql table (in attachment) but linked table
> > in Access see this as short text.
> >
> > I checked in odbc option Bools as Char to false and True is -1 as
> > true.but still tihs is not working.
>
> Define working.
>
> >
> > I found linke here:
> >
> > http://bahut.alma.ch/2006/04/access-odbc-postgresql-boolean-mess.html
> >
> > but this is very strange function there (which i am not understanding),
> > why to use it? There is no other option?
>
> Because Postgres has true boolean fields and Access does not.
>
> >
> > Best,
> > Jacek
> >
> >
> >
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: Disable TRUST authentication by using ClientAuthentication_hook

[Guillaume Lelarge]

2018-07-13 15:19 GMT+02:00 Fabrízio de Royes Mello 
:

>
> 2018-07-13 9:01 GMT-03:00 Guillaume Lelarge :
>
>> 2018-07-13 13:57 GMT+02:00 :
>>
>>> I’d like to disable the TRUST authentication method for certain servers
>>> where modification of pg_hba.conf and restarting a service is fairly easy
>>> for a number of users.
>>>
>>>
>>>
>>> I looked at this example https://wiki.postgresql.org/im
>>> ages/e/e3/Hooks_in_postgresql.pdf It appears that creating a
>>> ClientAuthentication_hook and call ereport(ERROR) in case that
>>> Port->HbaLine contains TRUST would do the job. Is that right?
>>>
>>>
>>>
>>> I am aware that this would not make the server entirely secure but it
>>> would make it at least a bit more difficult to enter.
>>>
>>>
>>>
>>
>> I'm not sure this is such a good idea. You may need the trust
>> authentication method, for example if you forgot the superuser password.
>> Otherwise, there's good chance you might use the ClientAuthentication hook
>> to do what you want.
>>
>>
>>
> If you're an server admin you can disable the extension (editing
> shared_pre_load_libraries GUC), change password and then enable the
> extension again...
>
>
That would require a restart.

And maybe you can implement a simple way to enable/disable this hook inside
> the extension.
>
>
That looks like a better idea.


-- 
Guillaume.

Re: Optimizing execution of expensive subqueries

[Mathieu Fenniak]

Hi Hellmuth,

Thanks for the response and the new approach; a LATERAL JOIN is new to me.
Unfortunately it seems to have the same performance characteristics and
query plan.  The aggregation in the lateral join still executes for every
row (eg. if my base query has 50 rows, I get "Aggregate (...
loops=50)" in the query plan), unaffected by the later LIMIT node in
the query plan.

The CTE approach seems to be the only one I can use to improve performance
right now, but requires significant application code changes.

Mathieu


On Wed, Jul 11, 2018 at 1:55 PM Hellmuth Vargas  wrote:

> Hi
>
> Try this way:
>
> SELECT
>   tbl.field1, tbl.field2, tbl.field3, ...,
>   b.Thingy1Sum,
>   ... repeat for multiply thingies ...
> FROM
>   tbl
>   LATERAL JOIN (
> SELECT anothertbl.UserId,SUM(Duration) as Thingy1Sum
> FROM anothertbl
> WHERE anothertbl.UserId = tbl.UserId AND anothertbl.ThingyId = 1
> group  by 1) as b on tbl.UserId=b.UserId
> ORDER BY tbl.field1 LIMIT 20
>
>
> El mié., 11 de jul. de 2018 a la(s) 09:25, Mathieu Fenniak (
> mathieu.fenn...@replicon.com) escribió:
>
>> Hi pgsql-general!
>>
>> I'm currently looking at a query that is generally selecting a bunch of
>> simple columns from a table, and also performing some subqueries to
>> aggregate related data, and then sorting by one of the simple columns and
>> paginating the result.
>>
>> eg.
>>
>> SELECT
>>   tbl.field1, tbl.field2, tbl.field3, ...,
>>   (SELECT SUM(Duration) FROM anothertbl WHERE anothertbl.UserId = tbl.UserId
>> AND anothertbl.ThingyId = 1) as Thingy1Sum,
>>   ... repeat for multiply thingies ...
>> FROM
>>   tbl
>> ORDER BY tbl.field1 LIMIT 20
>>
>> I'm finding that if "tbl" contains hundreds of thousands of rows, the
>> subqueries are being executed hundreds of thousands of times.  Because of
>> the sorting and pagination, this is appears to be unnecessary, and the
>> result is slow performance.  (PostgreSQL 9.5.9 server)
>>
>> I've only found one solution so far, which is to perform the sort &
>> pagination in a CTE, and the subqueries externally.  Are there any other
>> approaches that can be taken to optimize this and prevent the unnecessary
>> computation?
>>
>> CTE rewrite:
>>
>> WITH cte AS (
>> SELECT
>>   tbl.field1, tbl.field2, tbl.field3
>> FROM
>>   tbl
>> ORDER BY tbl.field1 LIMIT 20
>> )
>> SELECT cte.*,
>>   (SELECT SUM(Duration) FROM anothertbl WHERE anothertbl.UserId
>> = tbl.UserId AND anothertbl.ThingyId = 1) as Thingy1Sum,
>>   ... repeat for multiply thingies ...
>> FROM cte;
>>
>> Thanks for any thoughts you have,
>>
>> Mathieu Fenniak
>>
>
>
> --
> Cordialmente,
>
> Ing. Hellmuth I. Vargas S.
>
>
>

Re: Disable TRUST authentication by using ClientAuthentication_hook

[Fabrízio de Royes Mello]

2018-07-13 9:01 GMT-03:00 Guillaume Lelarge :

> 2018-07-13 13:57 GMT+02:00 :
>
>> I’d like to disable the TRUST authentication method for certain servers
>> where modification of pg_hba.conf and restarting a service is fairly easy
>> for a number of users.
>>
>>
>>
>> I looked at this example https://wiki.postgresql.org/im
>> ages/e/e3/Hooks_in_postgresql.pdf It appears that creating a
>> ClientAuthentication_hook and call ereport(ERROR) in case that
>> Port->HbaLine contains TRUST would do the job. Is that right?
>>
>>
>>
>> I am aware that this would not make the server entirely secure but it
>> would make it at least a bit more difficult to enter.
>>
>>
>>
>
> I'm not sure this is such a good idea. You may need the trust
> authentication method, for example if you forgot the superuser password.
> Otherwise, there's good chance you might use the ClientAuthentication hook
> to do what you want.
>
>
>
If you're an server admin you can disable the extension (editing
shared_pre_load_libraries GUC), change password and then enable the
extension again...

And maybe you can implement a simple way to enable/disable this hook inside
the extension.

Regards,

-- 
   Fabrízio de Royes Mello Timbira - http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

AW: Disable TRUST authentication by using ClientAuthentication_hook

[kpi6288]

 

> I'm not sure this is such a good idea. You may need the trust authentication 
> method, 

> for example if you forgot the superuser password. Otherwise, there's good 
> chance 

> you might use the ClientAuthentication hook to do what you want.

 

Thanks for your feedback. 

 

Klaus

 

Re: Database Refresh confusion

[Andreas Kretschmer]

Am 13.07.2018 um 13:10 schrieb Rijo Roy:

Hello Experts,

Today, my colleague asked me if there was any way to check the 
progress of recovery (backup-recovery) in Postgresql. I told him to 
check the postgresql logs and look out for keywords such as recovery. 
He was refreshing the database by restoring a database backup but the 
method he tried surprised me.

The below commands were used:
pg_basebackup -h remoteservername -p Port number -D /path -X s -c fast 
-n -P -v

And he just started the postgresql cluster using pg_ctl

I told him that he missed creating a recovery.conf with a recovery 
command,


that's not necessary, without recovery.conf postgres will start as a 
normal server.



but he told me that he always does this way.  I told him that the 
process is wrong and this will do a crash recovery and there are high 
chances of data corruption.


No, this way is safe


He replied saying he never faced an issue following his process of 
doing it without recovery.conf


2 questions:
1. Is this a good practice for refreshing data into a new server.
2. Is there any other mechanism to track the restoration process other 
than to check the postgresql logs.


pg_basebackup has a progress-feature, -P, see you command above




The postgresql version used in 10.0 on a Linux 6.9


the latest version is 10.4 and contains a lot of bugfixes, consider a 
update soon.



Regards, Andreas

--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com

Re: Disable TRUST authentication by using ClientAuthentication_hook

[Guillaume Lelarge]

2018-07-13 13:57 GMT+02:00 :

> I’d like to disable the TRUST authentication method for certain servers
> where modification of pg_hba.conf and restarting a service is fairly easy
> for a number of users.
>
>
>
> I looked at this example https://wiki.postgresql.org/images/e/e3/Hooks_in_
> postgresql.pdf It appears that creating a ClientAuthentication_hook and
> call ereport(ERROR) in case that Port->HbaLine contains TRUST would do the
> job. Is that right?
>
>
>
> I am aware that this would not make the server entirely secure but it
> would make it at least a bit more difficult to enter.
>
>
>

I'm not sure this is such a good idea. You may need the trust
authentication method, for example if you forgot the superuser password.
Otherwise, there's good chance you might use the ClientAuthentication hook
to do what you want.



-- 
Guillaume.

Disable TRUST authentication by using ClientAuthentication_hook

[kpi6288]

I'd like to disable the TRUST authentication method for certain servers
where modification of pg_hba.conf and restarting a service is fairly easy
for a number of users. 

 

I looked at this example
https://wiki.postgresql.org/images/e/e3/Hooks_in_postgresql.pdf It appears
that creating a ClientAuthentication_hook and call ereport(ERROR) in case
that Port->HbaLine contains TRUST would do the job. Is that right? 

 

I am aware that this would not make the server entirely secure but it would
make it at least a bit more difficult to enter. 

 

Thanks Klaus

Database Refresh confusion

[Rijo Roy]

Hello Experts, 
Today, my colleague asked me if there was any way to check the progress of 
recovery (backup-recovery) in Postgresql. I told him to check the postgresql 
logs and look out for keywords such as recovery. He was refreshing the database 
by restoring a database backup but the method he tried surprised me. The below 
commands were used:pg_basebackup -h remoteservername -p Port number -D /path -X 
s -c fast -n -P -vAnd he just started the postgresql cluster using pg_ctl 
I told him that he missed creating a recovery.conf with a recovery command, but 
he told me that he always does this way.  I told him that the process is wrong 
and this will do a crash recovery and there are high chances of data 
corruption. He replied saying he never faced an issue following his process of 
doing it without recovery.conf
2 questions:1. Is this a good practice for refreshing data into a new server. 
2. Is there any other mechanism to track the restoration process other than to 
check the postgresql logs. 
The postgresql version used in 10.0 on a Linux 6.9

Thanks, Rijo Roy 

Sent from Yahoo Mail on Android

Re: Read only to schema

[Łukasz Jarych]

Maybe read-only view?

Best,
Jacek

pt., 13 lip 2018 o 07:00 Łukasz Jarych  napisał(a):

> Hi Guys,
>
> Yesterday i tried all day to figure out system to read only schemas.
>
> I want to :
>
> 1. Create user who can login (user: jaryszek)
> 2. Create role who can read only data (only watching tables) (role:
> readonly)
> 3, Create role who can read all data (inserting, deleting, altering,
> dropping) (role: readall)
>
> What sqls should i use for this?
> What grants should i add?
>
> And now i am logged as jaryszek
>
> I want to grant myself role read only to schema public (when owner is
> postgres).
> I want to review tables as views only,
> After work i want to grant myself role readall to schema public.
>
> It is possible?
> Or possible workaround ?
>
> Best,
> Jacek
>

Re: Monitor repl slot size

[Achilleas Mantzios]

On 13/07/2018 13:19, Nicola Contu wrote:

Hello,
we used to monitor the replication slot size on postgres 9.6.6 with the 
following query:

SELECT pg_xlog_location_diff(pg_current_xlog_location(), restart_lsn) FROM 
pg_replication_slots WHERE slot_name = 'SLOT NAME';

We are moving to postgres 10.4 and we saw the pg_xlog_location_diff is not 
there anymore.


location -> lsn
xlog -> wal
SELECT pg_wal_lsn_diff(pg_current_wal_insert_lsn(), restart_lsn) FROM 
pg_replication_slots;

I know we can re-create it following this link : 
https://github.com/DataDog/integrations-core/issues/907

but, is there any better way to do it? Any replacement for that function on 
postgres 10?

Thanks a lot,
Nicola



--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

Monitor repl slot size

[Nicola Contu]

Hello,
we used to monitor the replication slot size on postgres 9.6.6 with the
following query:

SELECT pg_xlog_location_diff(pg_current_xlog_location(), restart_lsn) FROM
pg_replication_slots WHERE slot_name = 'SLOT NAME';

We are moving to postgres 10.4 and we saw the pg_xlog_location_diff is not
there anymore.

I know we can re-create it following this link :
https://github.com/DataDog/integrations-core/issues/907

but, is there any better way to do it? Any replacement for that function on
postgres 10?

Thanks a lot,
Nicola

Re: Create event triger

[Łukasz Jarych]

Hi Guys,

sorry for my late answer. I tested this today and working like a charm!

You are brilliant ! thank you, saved my ass!

Best,
Jacek

śr., 11 lip 2018 o 10:30 Ken Tanzer  napisał(a):

>
>
> On Tue, Jul 10, 2018 at 5:45 PM Adrian Klaver 
> wrote:
>
>> select add_trigger('trg_test');
>>
>> test=> \d trg_test
>>
>> Table "public.trg_test"
>>
>>
>>   Column |   Type| Collation | Nullable | Default
>>
>>
>> +---+---+--+-
>>
>>
>>   id | integer   |   |  |
>>
>>
>>   fld_1  | character varying |   |  |
>>
>>
>> Triggers:
>>
>>
>>  trg_test_change AFTER UPDATE ON trg_test FOR EACH STATEMENT EXECUTE
>> PROCEDURE ts_update()
>>
>>
> To take this a step further, if you really have a lot of tables and want
> to do it automatically, you could do something like this:
>
> SELECT table_name,add_trigger(table_name) FROM information_schema.tables 
> WHERE table_schema='public';
>
> This assumes that you want to add the trigger to _all_ your tables, and
> that you haven't made use of schemas and so your tables are all in the
> public schema.
> If that's not the case, you could adjust accordingly.  It would be safest
> to just pull the table names first, make sure the list is what you want,
> and then run with the add_trigger.  So start with this:
>
> SELECT table_name FROM information_schema.tables WHERE table_schema='public';
>
> and if the list of tables is what you want, then run with the add_trigger
> included.
>
> Cheers,
> Ken
>
>
> --
> AGENCY Software
> A Free Software data system
> By and for non-profits
> *http://agency-software.org/ *
> *https://demo.agency-software.org/client
> *
> ken.tan...@agency-software.org
> (253) 245-3801
>
> Subscribe to the mailing list
>  to
> learn more about AGENCY or
> follow the discussion.
>

Handlind booleans Postgresql-Access

[Łukasz Jarych]

Hi Guys,

i created boolean in postgresql table (in attachment) but linked table in
Access see this as short text.

I checked in odbc option Bools as Char to false and True is -1 as true.but
still tihs is not working.

I found linke here:

http://bahut.alma.ch/2006/04/access-odbc-postgresql-boolean-mess.html

but this is very strange function there (which i am not understanding), why
to use it? There is no other option?

Best,
Jacek

Re: Using always genereted

[Łukasz Jarych]

Hi,

thank you.
I think that i have to add sequance here...

Best,
Jacek

pt., 13 lip 2018 o 09:50 Guillaume Lelarge 
napisał(a):

> Hi,
>
> 2018-07-13 9:29 GMT+02:00 Łukasz Jarych :
>
>> hi,
>>
>> i am trying to alter table :
>>
>> ALTER TABLE logging.t_history
>>  alter column "id" GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
>>
>> but this is not working. Error i have.
>>
>> How to change this properly?
>>
>>
> Which error message do you get?
>
>
> --
> Guillaume.
>

Re: Using always genereted

[Guillaume Lelarge]

Hi,

2018-07-13 9:29 GMT+02:00 Łukasz Jarych :

> hi,
>
> i am trying to alter table :
>
> ALTER TABLE logging.t_history
>  alter column "id" GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
>
> but this is not working. Error i have.
>
> How to change this properly?
>
>
Which error message do you get?


-- 
Guillaume.

Using always genereted

[Łukasz Jarych]

hi,

i am trying to alter table :

ALTER TABLE logging.t_history
 alter column "id" GENERATED ALWAYS AS IDENTITY PRIMARY KEY,

but this is not working. Error i have.

How to change this properly?

Best,
Jacek