Re: Creating complex track changes database - challenge!

I attached what I have got so far. I will setup a shared repository these days. Zitat von Thiemo Kellner : Hi Lukasz I am working on a generic (reading the information schema and other database metadata), trigger based solution for SCD tables, i. e. tables that keep (or not according to

Re: Creating complex track changes database - challenge!

Hi Thiemo, you can share the repository, maybe when i will go more into PostgreSQL i would help you. *Regarding table versionig.* I am thinking about simple solution: 1. Create query or trigger which will be checking last date of inputed data within Table. 2. Export the table into seperate file/b

Re: Creating complex track changes database - challenge!

Hi Lukasz I am working on a generic (reading the information schema and other database metadata), trigger based solution for SCD tables, i. e. tables that keep (or not according to SCD type) history of the data. However, it is not far grown and I am not having much time to advance it so i

Re: Creating complex track changes database - challenge!

Thank you goeff. I need solution like this: Administrator push button or something like and adding comment (for bitbucket) that after creating update to database. Now whole database is exported to *.sql file, and commit with text provided by Admin. Can i connect using bash script to database and

Why is tuple_percent so low?

I am trying to refactor a table on disk so it consumes less space: Original is: create table post_timings( topic_id int not null, post_number int not null, user_id int not null, msecs int not null ) Target is: create table post_timings( post_id int not null, user_id int not nul

Re: is libpq and openssl 1.1.* compatible?

Thank you everyone who posted answers! I went back to openssl-1.0.2. On Mon, Feb 26, 2018 at 1:15 AM, Michael Paquier wrote: > On Mon, Feb 26, 2018 at 12:30:38AM -0700, Konstantin Izmailov wrote: > > Let me ask this differently: can Visual Studio 2013/2017 compile libpq > with > > openssl 1.1 su

Parallel Aware

​Hi, I'm trying to run my query in parallel mode. I have setup my tables with " WITH(parallel_workers=2)" storage parameter and I've created indexes for needed attributes. I have also set: max_worker_processes = 100 max_parallel_workers_per_gather = 2 max_parallel_workers = 100 However, when I us

Re: system catalog permissions

On Mon, Feb 26, 2018 at 7:50 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Mon, Feb 26, 2018 at 4:55 PM, Paul Jungwirth < > p...@illuminatedcomputing.com> wrote: > >> On 02/26/2018 03:47 PM, Tom Lane wrote: >> >>> PropAAS DBA writes: >>> We have a client which is segmenting

Re: Unexpected behavior with transition tables in update statement trigger

On Tue, Feb 27, 2018 at 4:18 AM, Tom Kazimiers wrote: > On Mon, Feb 26, 2018 at 11:15:44PM +1300, Thomas Munro wrote: >> On Sat, Feb 24, 2018 at 4:47 PM, Tom Kazimiers >> wrote: >> Thanks for the reproducer. Yeah, that seems to be a bug. >> nodeNamedTuplestorescan.c allocates a new read pointer

Re: system catalog permissions

On Mon, Feb 26, 2018 at 4:55 PM, Paul Jungwirth wrote: > On 02/26/2018 03:47 PM, Tom Lane wrote: > >> PropAAS DBA writes: >> >>> We have a client which is segmenting their multi-tenant cluster >>> (PostgreSQL 9.6) by schema, however if one of their clients connects via >>> pgadmin they see ALL s

Re: system catalog permissions

On 02/26/2018 03:47 PM, Tom Lane wrote: PropAAS DBA writes: We have a client which is segmenting their multi-tenant cluster (PostgreSQL 9.6) by schema, however if one of their clients connects via pgadmin they see ALL schemas, even the ones they don't have access to read. PG generally doesn't

Re: system catalog permissions

PropAAS DBA writes: > We have a client which is segmenting their multi-tenant cluster > (PostgreSQL 9.6) by schema, however if one of their clients connects via > pgadmin they see ALL schemas, even the ones they don't have access to > read. I assume pgadmin is pulling the list from the system c

Re: system catalog permissions

On 02/26/2018 03:11 PM, PropAAS DBA wrote: All; We have a client which is segmenting their multi-tenant cluster (PostgreSQL 9.6) by schema, however if one of their clients connects via pgadmin they see ALL schemas, even the ones they don't have access to read. I assume pgadmin is pulling the

system catalog permissions

All; We have a client which is segmenting their multi-tenant cluster (PostgreSQL 9.6) by schema, however if one of their clients connects via pgadmin they see ALL schemas, even the ones they don't have access to read. I assume pgadmin is pulling the list from the system catalogs. What's th

Re: Creating complex track changes database - challenge!

I would personally do that separately: write a bash script & cron job that does a schema dump every hour, and (if there are any changes) commits any changes to your schema repository; then you can use Github or Bitbucket web hooks to do stuff with the changeset when it’s pushed. https://stacko

Re: psql '\copy to' and unicode escapes

On Mon, Feb 26, 2018 at 9:53 AM, Steven Hirsch wrote: > I fear that I'm missing something very obvious, but I cannot find a syntax > that permits me to use an escaped hexadecimal representation in a CSV file > and have that representation interpreted as the equivalent unicode > character when ins

psql '\copy to' and unicode escapes

I fear that I'm missing something very obvious, but I cannot find a syntax that permits me to use an escaped hexadecimal representation in a CSV file and have that representation interpreted as the equivalent unicode character when inserting into the database. Both client and server are using UTF8

Re: Creating complex track changes database - challenge!

Thank you geoff! I think that i will test http://www.liquibase.org/ this one. what about setting up trigger to metadata (structural table) to find if column was added for example? Best, Jacek 2018-02-26 16:43 GMT+01:00 geoff hoffman : > > > There’s https://flywaydb.org/ > and http://www.liquib

Re: Creating complex track changes database - challenge!

I've got a manual method (though it's probably wise to go with a vendor product), that I will just dump here. It tracks all configured tables into a single table containing before/after record images in jsonb. create table aud_audit ( id serial8, timestamp

Re: Creating complex track changes database - challenge!

There’s https://flywaydb.org/ and http://www.liquibase.org/ More: https://dbmstools.com/version-control-tools Also, if you know PHP, Laravel database migrations have worked great for us! https://

Re: merge statement gives error

Abhra Kar schrieb am 26.02.2018 um 16:02: > Hi, > >        Trying to execute the following statement   > > > merge into ABC as n using dual on (n.id =123) > > when matched update set aaa=222, bbb=333 > >  when not matched insert (id, aaa) values (NEXTVAL(id),555); > > > > b

Re: Unexpected behavior with transition tables in update statement trigger

Hi Thomas, On Mon, Feb 26, 2018 at 11:15:44PM +1300, Thomas Munro wrote: On Sat, Feb 24, 2018 at 4:47 PM, Tom Kazimiers wrote: Thanks for the reproducer. Yeah, that seems to be a bug. nodeNamedTuplestorescan.c allocates a new read pointer for each separate scan of the named tuplestore, but it

merge statement gives error

Hi, Trying to execute the following statement merge into ABC as n using dual on (n.id=123) when matched update set aaa=222, bbb=333 when not matched insert (id, aaa) values (NEXTVAL(id),555); but gives syntax error.What should be the proper syntax[ Parameter values are properly

Re: Creating complex track changes database - challenge!

Hi Guys, I have idea already for creating this complex solution. Please give your notes and tips if you have. 1. Keep all changes within table including: -adding rows -deleting -editing This can be managed by adding triggers and one additional table where you can have sum up what was changed.

Re: Creating complex track changes database - challenge!

Łukasz Jarych schrieb am 26.02.2018 um 11:44: > i would like to ask you for help with track changes to my database.  > I am new to PosgtreeSQL but i have to learn it quickly because of my boss.  > > I have to: > > 1. Keep all changes within table including: > -adding rows > -deleting > -editing >

Re: Creating complex track changes database - challenge!

On Mon, Feb 26, 2018 at 11:44 AM Łukasz Jarych wrote: > I have to: > > 1. Keep all changes within table including: > -adding rows > -deleting > -editing > > 2. Save table with specific state and recover specific state (so go back > to previous table versions) including comparing tables. > > 3. Tr

Creating complex track changes database - challenge!

Hi Guys, i would like to ask you for help with track changes to my database. I am new to PosgtreeSQL but i have to learn it quickly because of my boss. I have to: 1. Keep all changes within table including: -adding rows -deleting -editing 2. Save table with specific state and recover specific s

Re: Unexpected behavior with transition tables in update statement trigger

On Sat, Feb 24, 2018 at 4:47 PM, Tom Kazimiers wrote: > I am on Postgres 10.2 and try to get a statement level trigger to work that > is executed after UPDATE statements on a particular table. This trigger > references both the old and new transition table and for some reason I am > unable to refe

Re: is libpq and openssl 1.1.* compatible?

On Mon, Feb 26, 2018 at 12:30:38AM -0700, Konstantin Izmailov wrote: > Let me ask this differently: can Visual Studio 2013/2017 compile libpq with > openssl 1.1 support? Under Windows? The answer to this question should be yes. (Please note that no Windows buildfarm machines use openssl 1.1.0 as