Import csv to temp table

[arun chirappurath]

Dear All,

Do we have any scripts that create a temp table with column names from the
first row of csv files?

any functions which we can pass the file name as parameter which loads the
data to csv based on the data

Thanks,
ACDBA

Testing - Please ignore/delete this message

[Arbol One]

Did it work?

--
ArbolOne.ca
Using Fire Fox and Thunderbird.
ArbolOne is composed of students and volunteers dedicated to providing free 
services to charitable organizations.
ArbolOne on Java Development is in progress [ í ]

Re: Import csv to temp table

[Ron Johnson]

On Tue, Jan 2, 2024 at 7:02 AM arun chirappurath 
wrote:

> Dear All,
>
> Do we have any scripts that create a temp table with column names from the
> first row of csv files?
>

How would you determine the data type?

If you assume TEXT for all of them, then it's relatively simple to write
bash which does it.

Re: Testing - Please ignore/delete this message

[Hilary Cotter]

Received!

On Tue, Jan 2, 2024, 8:13 AM Arbol One  wrote:

> Did it work?
>
> --
> ArbolOne.ca
> Using Fire Fox and Thunderbird.
> ArbolOne is composed of students and volunteers dedicated to providing
> free services to charitable organizations.
> ArbolOne on Java Development is in progress [ í ]
>
>
>
>

Re: Testing - Please ignore/delete this message

[Ray O'Donnell]

On 02/01/2024 13:12, Arbol One wrote:

Did it work?



No idea - I ignored it...! :-)

Ray.


--
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie

Re: Testing - Please ignore/delete this message

[David G. Johnston]

The way to check if your email was received by the list is to search for it
in the archives.

https://www.postgresql.org/search/?m=1

Please stop sending testing emails to the entire hundreds of thousands or
millions of people on this list.

David J.

On Tuesday, January 2, 2024, Ray O'Donnell  wrote:

> On 02/01/2024 13:12, Arbol One wrote:
>
>> Did it work?
>>
>>
> No idea - I ignored it...! :-)
>
> Ray.
>
>
> --
> Raymond O'Donnell // Galway // Ireland
> r...@rodonnell.ie
>
>
>
>

Re: Import csv to temp table

[Daniel Verite]

arun chirappurath wrote:

> Do we have any scripts that create a temp table with column names
> from the first row of csv files?

csvkit [1] does that.

[1] https://csvkit.readthedocs.io/en/latest/


Best regards,
-- 
Daniel Vérité
https://postgresql.verite.pro/
Twitter: @DanielVerite

New SET privilege for pg_has_role() in v16+

[Dominique Devienne]

Hi. And happy new year (for those using the Gregorian calendar).

pg_has_role() from
https://www.postgresql.org/docs/current/functions-info.html
added the 'SET' privilege in v16, and on top of the existing 'MEMBER' and
'USAGE' ones:

> MEMBER denotes direct or indirect membership in the role [...]
> USAGE denotes whether the privileges of the role are immediately
available without doing SET ROLE
> SET denotes whether it is possible to change to the role using the SET
ROLE command

I'd like to know if possible why SET was added; the rationale for it.
Does it not imply that MEMBER and USAGE weren't enough somehow before?

If `pg_has_role(..., 'MEMBER')` is true, isn't `pg_has_role(..., 'SET')`
implied?
If not, why? (and is that related to NOT INHERIT roles in the graph between
the two roles?)

Asked differently I guess, when does being a MEMBER of a role (directly or
not),
NOT allow SET ROLE to that role?

We use ROLEs extensively in our PostgreSQL-based apps,
and I've read a lot about them, but at times I feel I'm missing something.

Thanks, --DD

Re: Import csv to temp table

[Adrian Klaver]

On 1/2/24 06:51, Daniel Verite wrote:

arun chirappurath wrote:


Do we have any scripts that create a temp table with column names
from the first row of csv files?


csvkit [1] does that.

[1] https://csvkit.readthedocs.io/en/latest/


In addition to the above which I have used you might want to take a look at:

Polars
https://pola.rs/

and

Duckdb
https://duckdb.org/

They both allow you to query CSV(and other format) files directly. I 
have found them very useful for looking at CSV files and you can 
transfer data elsewhere later.


Then there is:

Pandas
https://pandas.pydata.org/pandas-docs/stable/index.html

which has 
read_csv(https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_csv.html) 
and 
to_sql(https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_sql.html#pandas.DataFrame.to_sql) 
to pull from a CSV into a table.






Best regards,


--
Adrian Klaver
adrian.kla...@aklaver.com

Re: New SET privilege for pg_has_role() in v16+

[Adrian Klaver]

On 1/2/24 07:24, Dominique Devienne wrote:

Hi. And happy new year (for those using the Gregorian calendar).

pg_has_role() from 
https://www.postgresql.org/docs/current/functions-info.html 

added the 'SET' privilege in v16, and on top of the existing 'MEMBER' 
and 'USAGE' ones:


 > MEMBER denotes direct or indirect membership in the role [...]
 > USAGE denotes whether the privileges of the role are immediately 
available without doing SET ROLE
 > SET denotes whether it is possible to change to the role using the 
SET ROLE command


I'd like to know if possible why SET was added; the rationale for it.
Does it not imply that MEMBER and USAGE weren't enough somehow before?

If `pg_has_role(..., 'MEMBER')` is true, isn't `pg_has_role(..., 'SET')` 
implied?
If not, why? (and is that related to NOT INHERIT roles in the graph 
between the two roles?)


Asked differently I guess, when does being a MEMBER of a role (directly 
or not),

NOT allow SET ROLE to that role?



https://www.postgresql.org/docs/current/sql-set-role.html

"Using this command, it is possible to either add privileges or restrict 
one's privileges. If the session user role has been granted memberships 
WITH INHERIT TRUE, it automatically has all the privileges of every such 
role. In this case, SET ROLE effectively drops all the privileges except 
for those which the target role directly possesses or inherits. On the 
other hand, if the session user role has been granted memberships WITH 
INHERIT FALSE, the privileges of the granted roles can't be accessed by 
default. However, if the role was granted WITH SET TRUE, the session 
user can use SET ROLE to drop the privileges assigned directly to the 
session user and instead acquire the privileges available to the named 
role. If the role was granted WITH INHERIT FALSE, SET FALSE then the 
privileges of that role cannot be exercised either with or without SET 
ROLE."





We use ROLEs extensively in our PostgreSQL-based apps,
and I've read a lot about them, but at times I feel I'm missing something.

Thanks, --DD


--
Adrian Klaver
adrian.kla...@aklaver.com

Re: New SET privilege for pg_has_role() in v16+

[David G. Johnston]

On Tue, Jan 2, 2024 at 8:25 AM Dominique Devienne 
wrote:

> Hi. And happy new year (for those using the Gregorian calendar).
>
> pg_has_role() from
> https://www.postgresql.org/docs/current/functions-info.html
> added the 'SET' privilege in v16, and on top of the existing 'MEMBER' and
> 'USAGE' ones:
>
> > MEMBER denotes direct or indirect membership in the role [...]
> > USAGE denotes whether the privileges of the role are immediately
> available without doing SET ROLE
> > SET denotes whether it is possible to change to the role using the SET
> ROLE command
>
> I'd like to know if possible why SET was added; the rationale for it.
> Does it not imply that MEMBER and USAGE weren't enough somehow before?
>
> If `pg_has_role(..., 'MEMBER')` is true, isn't `pg_has_role(..., 'SET')`
> implied?
> If not, why? (and is that related to NOT INHERIT roles in the graph
> between the two roles?)
>
> Asked differently I guess, when does being a MEMBER of a role (directly or
> not),
> NOT allow SET ROLE to that role?
>
> We use ROLEs extensively in our PostgreSQL-based apps,
> and I've read a lot about them, but at times I feel I'm missing something.
>
>
Membership no longer does anything by itself.  Both inherit and set
capabilities are now individually controlled permissions related to
membership.  It is indeed possible, but not useful, to grant membership but
then disallow both set and inherit permissions.

David J.

Re: New SET privilege for pg_has_role() in v16+

[Dominique Devienne]

On Tue, Jan 2, 2024 at 5:09 PM Adrian Klaver 
wrote:

> On 1/2/24 07:24, Dominique Devienne wrote:
> > pg_has_role() from
> > https://www.postgresql.org/docs/current/functions-info.html
> > 
> > added the 'SET' privilege in v16, and on top of the existing 'MEMBER'
> > and 'USAGE' ones:
>
> https://www.postgresql.org/docs/current/sql-set-role.html
>
> "[...], if the role was granted WITH SET TRUE [...]"
>

That seems to be the important part of your RFTM answer, which is
apparently new in v16.

Re: New SET privilege for pg_has_role() in v16+

[Dominique Devienne]

On Tue, Jan 2, 2024 at 5:11 PM David G. Johnston 
wrote:

> On Tue, Jan 2, 2024 at 8:25 AM Dominique Devienne 
> wrote:
>
>> pg_has_role() from
>> https://www.postgresql.org/docs/current/functions-info.html
>> added the 'SET' privilege in v16, and on top of the existing 'MEMBER' and
>> 'USAGE' ones:
>>
>

> Membership no longer does anything by itself.
>

OK! That's news to me, I must go back to the v16 (?) release notes and
learn more about this.


> Both inherit and set capabilities are now individually controlled
> permissions related to membership.
>

Hmmm, what drove this change? (I guess I'm getting back to the rationale
from earlier).
The previous model was not granular enough?
And the new one is as granular as it gets?

It is indeed possible, but not useful, to grant membership but then
> disallow both set and inherit permissions.
>

OK. Yet another thing I'll need to study.

As I wrote earlier, we use ROLEs extensively, some INHERIT and others NOT
INHERIT,
to map an existing C/C++ enforce security model in mid-tier services, to a
ROLE/GRANT-based
one enforced by PostgreSQL itself, thus understanding why these changes
were made in v16 matters to me a lot.

Thanks, --DD

Re: New SET privilege for pg_has_role() in v16+

[David G. Johnston]

On Tue, Jan 2, 2024 at 9:21 AM Dominique Devienne 
wrote:

> On Tue, Jan 2, 2024 at 5:11 PM David G. Johnston <
> david.g.johns...@gmail.com> wrote:
>
>> On Tue, Jan 2, 2024 at 8:25 AM Dominique Devienne 
>> wrote:
>>
>>> pg_has_role() from
>>> https://www.postgresql.org/docs/current/functions-info.html
>>> added the 'SET' privilege in v16, and on top of the existing 'MEMBER'
>>> and 'USAGE' ones:
>>>
>>
>
>> Membership no longer does anything by itself.
>>
>
> OK! That's news to me, I must go back to the v16 (?) release notes and
> learn more about this.
>
>
>> Both inherit and set capabilities are now individually controlled
>> permissions related to membership.
>>
>
> Hmmm, what drove this change? (I guess I'm getting back to the rationale
> from earlier).
> The previous model was not granular enough?
> And the new one is as granular as it gets?
>

Essentially yes.  Inherit used to be a property of a role and not a
specific membership which was deemed undesirable.  We were fixing up the
broken CREATEROLE attribute and felt these improvements were needed as
well.  Once inherit became optional per-membership it made sense to treat
set the same way.

David J.

Re: New SET privilege for pg_has_role() in v16+

[Adrian Klaver]

On 1/2/24 08:21, Dominique Devienne wrote:
On Tue, Jan 2, 2024 at 5:11 PM David G. Johnston 
mailto:david.g.johns...@gmail.com>> wrote:


On Tue, Jan 2, 2024 at 8:25 AM Dominique Devienne
mailto:ddevie...@gmail.com>> wrote:

pg_has_role() from
https://www.postgresql.org/docs/current/functions-info.html

added the 'SET' privilege in v16, and on top of the existing
'MEMBER' and 'USAGE' ones:

Membership no longer does anything by itself. 



OK! That's news to me, I must go back to the v16 (?) release notes and 
learn more about this.


Both inherit and set capabilities are now individually controlled
permissions related to membership.


Hmmm, what drove this change? (I guess I'm getting back to the rationale 
from earlier).

The previous model was not granular enough?
And the new one is as granular as it gets?

It is indeed possible, but not useful, to grant membership but then
disallow both set and inherit permissions.


OK. Yet another thing I'll need to study.

As I wrote earlier, we use ROLEs extensively, some INHERIT and others 
NOT INHERIT,
to map an existing C/C++ enforce security model in mid-tier services, to 
a ROLE/GRANT-based
one enforced by PostgreSQL itself, thus understanding why these changes 
were made in v16 matters to me a lot.


If you want the rationale see:

https://rhaas.blogspot.com/2023/01/surviving-without-superuser-coming-to.html



Thanks, --DD


--
Adrian Klaver
adrian.kla...@aklaver.com

Re: New SET privilege for pg_has_role() in v16+

[Adrian Klaver]

On 1/2/24 08:15, Dominique Devienne wrote:
On Tue, Jan 2, 2024 at 5:09 PM Adrian Klaver > wrote:


On 1/2/24 07:24, Dominique Devienne wrote:
 > pg_has_role() from
 > https://www.postgresql.org/docs/current/functions-info.html

 > >
 > added the 'SET' privilege in v16, and on top of the existing
'MEMBER'
 > and 'USAGE' ones:

https://www.postgresql.org/docs/current/sql-set-role.html


"[...], if the role was granted WITH SET TRUE [...]"


That seems to be the important part of your RFTM answer, which is 
apparently new in v16.


There also seems to an override:

https://www.postgresql.org/docs/16/runtime-config-client.html#GUC-CREATEROLE-SELF-GRANT

"createrole_self_grant (string)

If a user who has CREATEROLE but not SUPERUSER creates a role, and 
if this is set to a non-empty value, the newly-created role will be 
granted to the creating user with the options specified. The value must 
be set, inherit, or a comma-separated list of these. The default value 
is an empty string, which disables the feature.


The purpose of this option is to allow a CREATEROLE user who is not 
a superuser to automatically inherit, or automatically gain the ability 
to SET ROLE to, any created users. Since a CREATEROLE user is always 
implicitly granted ADMIN OPTION on created roles, that user could always 
execute a GRANT statement that would achieve the same effect as this 
setting. However, it can be convenient for usability reasons if the 
grant happens automatically. A superuser automatically inherits the 
privileges of every role and can always SET ROLE to any role, and this 
setting can be used to produce a similar behavior for CREATEROLE users 
for users which they create.

"

--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Import csv to temp table

[Adrian Klaver]

On 1/2/24 11:47, arun chirappurath wrote:

Reply to list
Ccing list

Hi Adrian,

Love this tool..however it doesn't like supporting RDS.


1) This was Daniel Vérité's suggestion not mine.

2) Define "... doesn't like supporting RDS".

a) You can generate an SQL statement without connecting to the database.
Then use that statement directly in the database.

b) If SQLAlchemy can reach the database then csvkit should be able to.



https://csvkit.readthedocs.io/en/latest/ 



Regards
Arun




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Logical replication breaks: "unexpected duplicate for tablespace 0, relfilenode 2774069304"

[Michael Paquier]

On Thu, Dec 28, 2023 at 02:03:12PM +0200, Kouber Saparev wrote:
>> The first problem that we have here is that we've lost track of the
>> patch proposed, so I have added a CF entry for now:
>> https://commitfest.postgresql.org/46/4720/
> 
> Thank you. Is there a bug report or should we file one? It looks like
> something that compromises the reliability of the logical replication as a
> whole.

Having a CF entry means that it is already tracked, so no need to do
more here for the moment.  The next step would be to look at the
proposed patch in more details, and work on fixing the issue.
--
Michael


signature.asc
Description: PGP signature

Re: encoding option when database backup

[rob stone]

On Wed, 2024-01-03 at 15:59 +0900, Taek Oh wrote:
> Hi there,
> 
> I would like to make an inquiry regarding the encoding option for the
> database backup.
> When I was using the previous version of PGADMIN 4, I had a
> dropbar for the encoding format for the backup option.
> But I recently started using the latest version of PGADMIN 4(V8.1),
> and I realized that the dropbar for encoding format has disappeared
> as we can observe from the attached image.
> Are there any solutions where I can activate the encoding dropbar?
> 
> Thank you in advance,
> 
> Taek
> 
> 


See https://www.pgadmin.org/docs/pgadmin4/latest/backup_dialog.html 
where it mentions:-

Use the Encoding drop-down listbox to select the character encoding
method that should be used for the archive.

Sample data generator for performance testing

[arun chirappurath]

Hi All,

Do we have any open source tools which can be used to create sample data at
scale from our postgres databases?
Which considers data distribution and randomness

Regards,
Arun