C function returning a tuple containing an array of tuples

[Alberto]

Hello,

I have a function written in C language returning a tuple. One of the fields of
that tuple is an array of tuples.

I obtain the TupleDesc of the outer tuple using get_call_result_type(fcinfo, ...
and is working fine.

I can't figure how to obtain the TupleDesc of the tuple of the array (the 4th
attribute of the outer tuple, in my case).
For example, fcinfo->args[] array has some information about the attributes but
I haven't found anything useful to call get_call_result_type().

Any suggest?

Best regards

Alberto

[no subject]

[Alberto García Fumero]

Good morning to all.

I'd need some advice.

I'm trying to install PgAdmin4  from its  repository, on Debian 11,
by the instructions in the concerning page in www.postgresql.org. The
installation fails, as the packages cannot be verified. What shoud I
do? I tried using  deb [trusted=yes], but understandably (https) that
doesn't work.

Fumero

Re: duplicate key value violates unique constraint "chinese_price_infos_pkey"

[Frank Alberto Rodriguez]

You can fix the problem with this query:

SELECT setval('chinese_price_infos_id_seq', sq.val) from ( SELECT
MAX(id) as val FROM chinese_price_infos ) sq;

But you have to search in your application because in some point the
app are inserting the id column instead of leave this task to the DB. 
If you are using some entity framework and the app fill the ID field in
a new entity in some point of the workflow, then when you save the
entity, the framework automatically saves the ID in the DB without
checking if the ID already exist.


Sheers

On Mon, 2019-05-06 at 16:40 +0530, Arup Rakshit wrote:
> Hi,
> 
> Thanks for your reply. It is automatic, my app don’t creates ID, it delegates 
> it to the DB. I am using Ruby on Rails app, where we use Postgresql. 
> 
> docking_dev=# \d chinese_price_infos;
>  Table "public.chinese_price_infos"
>Column|Type | Collation | Nullable |   
>   Default 
> -+-+---+--+-
>  id  | integer |   | not null | 
> nextval('chinese_price_infos_id_seq'::regclass)
>  created_at  | timestamp without time zone |   |  | 
>  updated_at  | timestamp without time zone |   |  | 
>  item_code   | character varying(255)  |   |  | 
>  description | character varying(255)  |   |  | 
>  unit| character varying(255)  |   |  | 
>  price_cents | integer |   |  | 
>  uuid| uuid|   |  | 
> uuid_generate_v4()
>  company_id  | uuid|   |  | 
> Indexes:
> "chinese_price_infos_pkey" PRIMARY KEY, btree (id)
> "index_chinese_price_infos_on_company_id" btree (company_id)
> 
> 
> 
> 
> Thanks,
> 
> Arup Rakshit
> a...@zeit.io
> 
> 
> 
> On 06-May-2019, at 4:38 PM, Ray O'Donnell  wrote:
> 
> On 06/05/2019 12:05, Arup Rakshit wrote:
> Every time I try to insert I get the error:
> docking_dev=# INSERT INTO "chinese_price_infos" ("item_code",
> "price_cents", "unit", "description", "company_id", "created_at",
> "updated_at") VALUES ('01GS10001', 6000, 'Lift', 'Shore Crane
> Rental', '9ae3f8b8-8f3f-491c-918a-efd8f5100a5e', '2019-05-06
> 10:49:03.894725', '2019-05-06 10:49:03.894725'); ERROR:  duplicate
> key value violates unique constraint "chinese_price_infos_pkey" DETAIL:  Key 
> (id)=(71165) already exists. docking_dev=# INSERT INTO
> "chinese_price_infos" ("item_code", "price_cents", "unit",
> "description", "company_id", "created_at", "updated_at") VALUES
> ('01GS10001', 6000, 'Lift', 'Shore Crane Rental',
> '9ae3f8b8-8f3f-491c-918a-efd8f5100a5e', '2019-05-06 10:49:03.894725',
> '2019-05-06 10:49:03.894725'); ERROR:  duplicate key value violates
> unique constraint "chinese_price_infos_pkey" DETAIL:  Key
> (id)=(71166) already exists.
> Then I found:
> docking_dev=# SELECT MAX(id) FROM chinese_price_infos; max  128520 (1 
> row)
> docking_dev=# SELECT nextval('chinese_price_infos_id_seq'); nextval - 
> 71164 (1 row)
> Not sure how it is out of sync. How can I fix this permanently. I ran
> vacuum analyze verbose; still same error.
> 
> 
> You can fix it by using setval() to set the sequence manually to something 
> higher than the highest current id value in the table. However, it sounds as 
> if something in the application code may be causing problems For example, 
> is something generating id values without reference to the sequence?
> 
> Ray.
> 
> 
> 
> -- 
> Raymond O'Donnell // Galway // Ireland
> r...@rodonnell.ie
> 
> 
> 
> 

Re: PITR based recovery in a primary/standby cluster setup

[Frank Alberto Rodriguez]

Hello.I'm not sure which replications issues you have, and I never used
Wall-E before, but I get some issues with PotgreSql 10 and Barman.
Try starting the primary server at first, when it finish to recovery
this should start as primary, if not then go to the postgresql data
directory and rename the recovery.conf to recovery.done and start the
server as primary. 
Then start the standby server and when  recovery target  is reached,
the standby server should not leave the recovery status and this should
weep receiving wal through the archive_command, and should not rename
the recovery.conf file.
Regards
On Tue, 2019-05-21 at 14:27 +0530, Abhijit Gharami wrote:
> Hi,
> 
> We have primary and standby PostgreSQL cluster setup and also we have
> PITR enabled on it. To improve the recovery time we are thinking of
> recovering the database to both primary and standby at the same time.
> 
> These are the steps we are following:
>   1. Restore the base backup to the both primary and standby server 
>   2. Replay the WAL files on both primary and standby and once the
> recovery target is reached stop the servers 
>   3. Start one of the server as Primary and other one as standby
> 
> 
> We have followed the above steps but when we are trying to start the
> servers as primary and standby we are having replication issues.
> 
> Could you please suggest what should be done here so that we can
> recover the database in both primary as well as in  standby server?
> 
> We are using PostgrerSQL version: 9.6.12 and for PITR we are using
> WAL-E.
> 
> Regards,
> Abhijit

Re: Data entry / data editing tools (more end-user focus).

[Frank Alberto Rodriguez]

If you have the database modeled, the most quickly think I can thinks
is with python framework Django. Configure the connection to the DB and
make reverse engineer with Django, this create the entities class,
then  activate the administration forms and configure each form for the
entities (few lines) and Django makes the magic and makes the GUI for
the DB and if the entities are related this give you the option to
insert before the entities needed. As a plus, you have the access
control module done too.
Regards 
On Thu, 2019-05-23 at 11:52 +0200, Tony Shelver wrote:
> I looked at quite a few options.   Some constraints on my side that
> our direction is open source, with Linux development and servers.
> Radzen is .NET:  I could just as well use MS Access to cobble
> together a front end.
> 
> CUBA and OpenXava are Java based and seem to require writing Java for
> logic: I last used nearly 20 years ago and 'fast' development and
> Java IMHO is an oxymoron.
> 
> Aurelia looks a bit promising, but I am not sure if it gains anything
> over the current crop of JS libraries and frameworks, such as Vue,
> React et al, and then libraries / frameworks built on top of those
> such as  Nuxt / Vue, Quasar / Vue or Vuetify / Vue, which seem to
> have far more activity on Github.
> 
> I managed to set up a quick and dirty front end using LibreOffice
> Base over a weekend, next iteration i will probably move over to a
> Vue framework, probably using Quasar..
> 
> On Sat, 18 May 2019 at 00:26, Stefan Keller 
> wrote:
> > Dear all
> > 
> > 
> > 
> > What about following „Rapid App Development Tools"?
> > 
> > * OpenXava (Java): 
> > https://www.openxava.org/ate/visual-studio-lightswitch
> > 
> > * Radzen (.NET): 
> > https://www.radzen.com/visual-studio-lightswitch-alternative/
> > 
> > * Other: https://aurelia.io/ (JS) or CUBA 
> > https://www.cuba-platform.com/ (Java)
> > 
> > 
> > 
> > :Stefan
> > 
> > 
> > 
> > Am Do., 28. März 2019 um 15:39 Uhr schrieb Adrian Klaver
> > 
> > :
> > 
> > >
> > 
> > > On 3/27/19 11:49 PM, Tony Shelver wrote:
> > 
> > >
> > 
> > > Please reply to list also, more eyes on the the problem.
> > 
> > > Ccing list
> > 
> > >
> > 
> > > My take on below is since you are feeding a Website why not use
> > Web
> > 
> > > technologies for your data entry. My language of choice is
> > Python. I
> > 
> > > have done something similar to this(on small scale) using the
> > Django
> > 
> > > framework. For something lighter weight there is Flask. Then your
> > client
> > 
> > > becomes a browser and you do not have to distribute forms around.
> > You
> > 
> > > could integrate with the existing Web apps you are using e.g.
> > SnipCart.
> > 
> > >
> > 
> > >
> > 
> > > > Actually I found a possibility.  LibreOffice Base on top of PG
> > lets me
> > 
> > > > paste photos into a Postgresql bytea field no problem.  MS
> > Access should
> > 
> > > > work well also, but I am not going to buy it, and running
> > Ubuntu most of
> > 
> > > > the time.
> > 
> > > > Possibly will distribute the Base forms to select users to
> > enter data.
> > 
> > > > We are a startup company, so this is an affordable temporary
> > fix, until
> > 
> > > > the product I have been looking at matures, or we can roll our
> > own.
> > 
> > > >
> > 
> > > > We are building a company website, including an eStore, and
> > have a few
> > 
> > > > hundred products to load and maintain. Our product data
> > currently isn't
> > 
> > > > suitable for a sales catalog.
> > 
> > > > (Brands, categories, products, pricing and deal info, specials,
> > images,
> > 
> > > > product comparisons and so on).
> > 
> > > >
> > 
> > > > Right now I input / maintain this via CSV files maintained
> > through a
> > 
> > > > spreadsheet  (LibreOffice Calc) which our site generator
> > (Jekyll) uses
> > 
> > > > to build out the static HTML product [pages automatically.
> > 
> > > > This is really quick to enter basic data, but I have to
> > manually
> > 
> > > > maintain image uploads, image names and so on manually in the
> > 
> > > > spreadsheet and through individual file uploads. We have at
> > least one,
> > 
> > > > preferably 3 and up to 6 photos per product to maintain.  Call
> > it a 1000
> > 
> > > > images right now, and that will only go up.
> > 
> > > > Invalid text / characters in product descriptions and so on can
> > break
> > 
> > > > the CSV as well.
> > 
> > > >
> > 
> > > > There are headless CMS solutions out on the market targeting
> > this same
> > 
> > > > area, but for various reasons the suitable ones are still
> > maturing and
> > 
> > > > shaking out in the marketplace, so I am not in a hurry to make
> > a choice.
> > 
> > > >
> > 
> > > > So the goal is to replace CSV with JSON file input.  This will
> > also make
> > 
> > > > my life easier for more complex structures such as multiple
> > categories
> > 
> > > > and specs per product.
> > 
> > > > I also want to migrate text that can change from the HTML pages
> > into the
> > 
> 

Re: One way replication in PostgreSQL

[Frank Alberto Rodriguez]

You could use FDW to replicate what you need to an external server from
the provider/primary/master to the subscriber/secondary/slaveUsing
triggers on the master tables that you want to replicate, you can
execute the insert/update/delete actions on the secondary tables
through the FDW.With this approach you only need a connection from
provider to the subscriber.
Regards  On Mon, 2019-06-03 at 18:00 +0200, PALAYRET Jacques wrote:
> Hello,
> 
> If, for security reasons, I can't create a connection or a flow from
> subscriber/secundary/slave towards provider/primary/master, witch
> replication systems can I use ?
> 
> If possible, I would prefer partial replication (only some tables) to
> full base replication (all instances).
> 
> Do trigger-based replication systems (like Slony or Londiste or
> others) need a connection or flow from subscriber to the provider ?
> 
> Thanks in advance
> - Météo-France -
> PALAYRET JACQUES
> DCSC/MBD 
> jacques.palay...@meteo.fr 
> Fixe : +33 561078319

Re: One way replication in PostgreSQL

[Frank Alberto Rodriguez]

The FDW is a PostgreSQL extension  to connect to other server from
PosgreSQL server inside, with this solution you only need connections
from P to S and no need a third server (external server), just use
triggers to push the INSERT/UPDATE/DELETE information you want to
replicate from P to S through Foreign Data Wrapper.
If you need integrity on the replicated information then I suggest to
use a control table to store the actions to replicate for the case when
it fails you can keep trying til the action succeeds.
Regards 
On Tue, 2019-06-04 at 09:02 +0200, PALAYRET Jacques wrote:
> Hello,
> 
> Thanks a lot for the suggested solutions.
> 
> So, I can use WAL-shipping replication from Primary to the Secundary
> server, but it's only for full replication.
> 
> Let's call " P " the provider/primary/master  and " S " the
> subscriber/secundary/slave one.
> For partial replication (not all the tables), the solutions should
> use a third (intermediate / middle) server which could have both ways
> flow with the server P but only one way flow towards the server S.
> For example, a logical replication (pglogical or logical Postgresql
> replication) between server P and the middle server and then a WAL-
> shipping replication between middle server and server S.
> Is that right ?
> 
> About the " FDW " solution in " an external server " (a middle one),
> is it possible to integrate the FDW in the P server to avoid the "
> external server " ?
> 
> => What about the trigger-based replication systems like Slony or
> Londiste ; is it really necessary to have a connection or flow from
> the server S towards the server P ?
> 
> Regards
> De: "PALAYRET Jacques" 
> À: pgsql-general@lists.postgresql.org
> Envoyé: Lundi 3 Juin 2019 18:00:51
> Objet: One way replication in PostgreSQL
> 
> Hello,
> 
> If, for security reasons, I can't create a connection or a flow from
> subscriber/secundary/slave towards provider/primary/master, witch
> replication systems can I use ?
> 
> If possible, I would prefer partial replication (only some tables) to
> full base replication (all instances).
> 
> Do trigger-based replication systems (like Slony or Londiste or
> others) need a connection or flow from subscriber to the provider ?
> 
> Thanks in advance
> - Météo-France -
> PALAYRET JACQUES
> DCSC/MBD 
> jacques.palay...@meteo.fr 
> Fixe : +33 561078319

Re: Forks of pgadmin3?

[Frank Alberto Rodriguez Solana]

You could try OmniDB, is web app but have a version that just feels like a
desktop application. Is supported by 2ndQuadrant.

This is the official website  https://omnidb.org/en/

Greetings

El vie., 22 mar. 2019 a las 4:56, Christian Henz ()
escribió:

> I know I'm late to the party, but we're only now migrating from
> Postgres 9.x, realizing that pgadmin3 does not support Postgres 11.
>
> I have checked out pgadmin4, but I don't like it at all. My colleagues
> feel the same way, and some web searching suggests that we are not
> alone.
>
> So I wonder if there are any active forks of pgadmin3?
>
> I found some on Github with some significant changes that I assume
> were done by people working for VK, the Russian social network. These
> appear to be personal hacks though (monosyllabic commit messages, build
> scripts added with hard coded local paths etc.).
>
> There are also the Debian packages that have patches adding Postgres
> 10 support among other things. Not sure if there would be interest
> there in continuing to support newer Postgres versions.
>
> Are there other, more organized efforts to continue pgadmin3?
>
> Are there technical reasons why such a continuation would not make
> sense?
>
> Cheers,
> Christian
>
> --
> Christian Henz
> Software Developer, software & vision Sarrazin GmbH & Co. KG
>
>
>

PostgreSQL Kerberos Authentication

[HIRTZ Jorge Alberto TENARIS]

Hello All,

I am trying to configure PostgreSQL9.6 (On Centos 7.4) with Kerberos (Active 
Directory) via GSSAPI authentication and I'm getting the following error:

[postgres@hostname data]$ psql  -h hostname -U usern...@domain.com postgres
psql: GSSAPI continuation error: Unspecified GSS failure.  Minor code may 
provide more information
GSSAPI continuation error: Server not found in Kerberos database

I did the following configuration:

1.- Create KeyTab in Active Directory:
ktpass -out postgres_instance.keytab -princ 
postgres/hostnamename.domain@domain.com -mapUser svcPostgres -pass 
 -crypto all -ptype KRB5_NT_PRINCIPAL

2.- Copy the keytab to Linux Server on $PGDATA and change the privileges to 
postgres:postgres
3.- Configure postgresql.conf
krb_server_keyfile = '//data/postgres_instance.keytab

4.- Configure /etc/krb5.conf

5.- Request a ticket to the KDC server using kinit (this work OK!)

[postgres@hostname ~]$ klist
Ticket cache: KEYRING:persistent:26:krb_ccache_AO0Y1kx
Default principal: usern...@domain.com

Valid starting   Expires  Service principal
01/30/2018 11:01:59  01/30/2018 21:01:59  krbtgt/domain@domain.com
renew until 02/06/2018 11:01:55


6.- Configure pg_hba.conf
hostallall
0.0.0.0/0  gss include_realm=1
7.- Create user in PG to test:
create user "usern...@domain.com" WITH SUPERUSER;

8.- Testing
[postgres@hostname data]$ psql  -h hostname -U usern...@domain.com postgres
psql: GSSAPI continuation error: Unspecified GSS failure.  Minor code may 
provide more information
GSSAPI continuation error: Server not found in Kerberos database

I tried generate the Keytab with "postgres" and "POSTGRES" user as a SPN but I 
get the same error.

Any suggestion is welcome!

Thanks in advance for your help!

Jorge

RE: PostgreSQL Kerberos Authentication

[HIRTZ Jorge Alberto TENARIS]

Thanks Poul,

According to official documentation parameters like ldap, and suffix in 
pg_hba.conf are for LDAP Authentication not for Kerberos/GSSAPI. In fact the 
authentication through LDAP works fine in our environment but not for Kerberos…

Do you know if the principal in Active Directory KDC must be in uppercase or 
lowercase? POSTGRES or postgres? Just to confirm.

Thanks
Jorge





From: EXTERNAL:Poul Kristensen [mailto:bcc5...@gmail.com]
Sent: martes, 30 de enero de 2018 01:50 p.m.
To: HIRTZ Jorge Alberto TENARIS 
Cc: pgsql-general@lists.postgresql.org
Subject: Re: PostgreSQL Kerberos Authentication

you need til tell Postgresql/pg_hba.conf  the AD kerberos server name ldap = 
kerberos.domain.com<http://kerberos.domain.com>   and suffix
@domain.com<http://domain.com>

Then create the users(is in fact a role) as the owner of a database.
Hereafter the user could just write psql after login and after password 
auhtentication the user/role is logged into the database.

It has been testet and works!

Hope it is usefull.

regards
Poul

2018-01-30 17:13 GMT+01:00 HIRTZ Jorge Alberto TENARIS 
mailto:jhi...@tenaris.com>>:
Hello All,

I am trying to configure PostgreSQL9.6 (On Centos 7.4) with Kerberos (Active 
Directory) via GSSAPI authentication and I’m getting the following error:

[postgres@hostname data]$ psql  -h hostname -U 
usern...@domain.com<mailto:usern...@domain.com> postgres
psql: GSSAPI continuation error: Unspecified GSS failure.  Minor code may 
provide more information
GSSAPI continuation error: Server not found in Kerberos database

I did the following configuration:

1.- Create KeyTab in Active Directory:
ktpass -out postgres_instance.keytab -princ 
postgres/hostnamename.domain@domain.com<mailto:hostnamename.domain@domain.com>
 -mapUser svcPostgres -pass  -crypto all -ptype KRB5_NT_PRINCIPAL

2.- Copy the keytab to Linux Server on $PGDATA and change the privileges to 
postgres:postgres
3.- Configure postgresql.conf
krb_server_keyfile = '//data/postgres_instance.keytab

4.- Configure /etc/krb5.conf

5.- Request a ticket to the KDC server using kinit (this work OK!)

[postgres@hostname ~]$ klist
Ticket cache: KEYRING:persistent:26:krb_ccache_AO0Y1kx
Default principal: usern...@domain.com<mailto:usern...@domain.com>

Valid starting   Expires  Service principal
01/30/2018 11:01:59  01/30/2018 21:01:59  
krbtgt/domain@domain.com<mailto:domain@domain.com>
renew until 02/06/2018 11:01:55


6.- Configure pg_hba.conf
hostallall
0.0.0.0/0<http://0.0.0.0/0>  gss include_realm=1
7.- Create user in PG to test:
create user “usern...@domain.com<mailto:usern...@domain.com>” WITH SUPERUSER;

8.- Testing
[postgres@hostname data]$ psql  -h hostname -U 
usern...@domain.com<mailto:usern...@domain.com> postgres
psql: GSSAPI continuation error: Unspecified GSS failure.  Minor code may 
provide more information
GSSAPI continuation error: Server not found in Kerberos database

I tried generate the Keytab with “postgres” and “POSTGRES” user as a SPN but I 
get the same error.

Any suggestion is welcome!

Thanks in advance for your help!

Jorge




--
Med venlig hilsen / Best regards
Poul Kristensen
Linux-OS/Virtualizationexpert and Oracle DBA

RE: PostgreSQL Kerberos Authentication

[HIRTZ Jorge Alberto TENARIS]

Thank you Peter!, I will check DNS configuration.
Regards
Jorge


-Original Message-
From: EXTERNAL:Peter Eisentraut [mailto:peter.eisentr...@2ndquadrant.com] 
Sent: miércoles, 31 de enero de 2018 01:33 a.m.
To: HIRTZ Jorge Alberto TENARIS ; 
pgsql-general@lists.postgresql.org
Subject: Re: PostgreSQL Kerberos Authentication

On 1/30/18 11:13, HIRTZ Jorge Alberto TENARIS wrote:
> [postgres@hostname data]$ psql  -h hostname -U usern...@domain.com 
> postgres
> 
> psql: GSSAPI continuation error: Unspecified GSS failure.  Minor code 
> may provide more information
> 
> GSSAPI continuation error: Server not found in Kerberos database

Check that your DNS resolves everything correctly.

You can find some ideas about this error in the internet.  It's not a problem 
specific to PostgreSQL.  It looks like you have things set up correctly.

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services