Re: How does pg parse 'select '(1,2)''

[Kyotaro Horiguchi]

At Sat, 5 Aug 2023 21:46:33 +0800, jacktby jacktby  wrote in 
> I’m trying to add new grammar for my application. So I’m do research on 
> gram.y.
> I think it will make the '(1,2)' as " '(' a_expr ')' "; but I can’t find out 
> something like
> " a_expr ',' a_expr “, can you give me the details?

If you are trying literally "SELECT (1, 2)", I think that the parser
comes to the terminating a_expr through the following path.


simple_select: SELECT .. opt_target_list ...
opt_target_list: target_list
target_list: target_el | target list '.' target_el
target_el: a_expr
a_expr: c_expr
c_expr: implicit_row
implicit_row: '(' expr_list ',' a_expr ')'
expr_list: a_expr | expr_list ',' a_expr

regards.

-- 
Kyotaro Horiguchi
NTT Open Source Software Center

Re: Dropping all tables in a database

[Thomas Kellerer]

H schrieb am 07.08.2023 um 03:17:

> I am running PostgreSQL  13.11 and tried to drop all tables in a
> database without dropping the database or schema. After logging in as
> the correct user, the following SQL statement does not work:
>
> SELECT 'DROP TABLE IF EXISTS "' || tablename || '" CASCADE;' FROM
> pg_tables WHERE schemaname = 'public' AND tableowner = 'xxx';
>
> The above statement does not drop any tables, nor are there any error
> messages.
>
> SELECT * FROM pg_tables;
>
> The above shows all tables are still present in the database.
>
> Dropping individual tables works fine but since I need to drop all
> tables in the database in a develop environment, this is not
> workable.
>
> I had to resort to the following:
>
> -- turn off headers: \t SELECT 'DROP TABLE IF EXISTS "' || tablename
> || '" CASCADE;' FROM pg_tables WHERE schemaname = 'public' AND
> tableowner = 'livraddarpaket'; \g out.tmp \i out.tmp
>
> The SQL statements above run fine.
>
> Is there some setting I have to change in the database to have the
> first SQL statement to work or have I run into a possible bug?

David already mentioned that you can use \gexec instead of the ;
to run the generated statements directly.

Does that user have other objects (e.g. types or sequences) as well?
Maybe "DROP OWNED BY xxx;" is an alternative?

However, that would really drop _everything_ that the users own - not just 
tables.

In my experience one usually wants to get rid of the other things as well.

Re: My 1st TABLESPACE

[negora]

Hi:

Although the "postgres" user owns the "data" directory, Has he access to 
the whole branch of directories? Maybe the problem is that he can't 
reach the "data" directory.


Regards.


On 07/08/2023 07:43, Amn Ojee Uw wrote:


I'd like to create a TABLESPACE, so, following this web page 
,  
I have done the following :


*mkdir 
/home/my_debian_account/Documents/NetbeansWorkSpace/JavaSE/Jme/database/postgresql/data*


*sudo chown postgres:postgres 
/home/my_debian_account/Documents/NetbeansWorkSpace/JavaSE/Jme/database/postgresql/data*


*sudo -u postgres psql*

*\du**
** arbolone    | Cannot login  | {}**
** chispa |    
| {prosafe}**

** workerbee | Superuser, Create DB | {arbolone}**
** jme |    | 
{arbolone}**
** postgres    | Superuser, Create role, Create DB, Replication, 
Bypass RLS | {}**

** prosafe  | Cannot login  | {}**

**CREATE TABLESPACE jmetablespace OWNER jme LOCATION 
'/home/my_debian_account/Documents/NetbeansWorkSpace/JavaSE/Jme/database/postgresql/data';*


The *CREATE **TABLESPACE* schema throws this error message :

/*ERROR:  could not set permissions on directory 
"/home/my_debian_account/Documents/NetbeansWorkSpace/JavaSE/Jme/database/postgresql/data": 
Permission denied*/


I have followed the web page to the best of my abilities, and AFAIK, 
the postgres user owns the folder '*data*'.


I know that something is missing, where did I go wrong and how can I 
resolve this issue?



Thanks in advance.

My 1st TABLESPACE

[Amn Ojee Uw]

I'd like to create a TABLESPACE, so, following this web page 
,  I 
have done the following :


*mkdir 
/home/my_debian_account/Documents/NetbeansWorkSpace/JavaSE/Jme/database/postgresql/data*


*sudo chown postgres:postgres 
/home/my_debian_account/Documents/NetbeansWorkSpace/JavaSE/Jme/database/postgresql/data*


*sudo -u postgres psql*

*\du**
** arbolone    | Cannot login  | {}**
** chispa |    | 
{prosafe}**

** workerbee | Superuser, Create DB    | {arbolone}**
** jme |    | 
{arbolone}**
** postgres    | Superuser, Create role, Create DB, Replication, Bypass 
RLS | {}**

** prosafe  | Cannot login  | {}**

**CREATE TABLESPACE jmetablespace OWNER jme LOCATION 
'/home/my_debian_account/Documents/NetbeansWorkSpace/JavaSE/Jme/database/postgresql/data';*


The *CREATE **TABLESPACE* schema throws this error message :

/*ERROR:  could not set permissions on directory 
"/home/my_debian_account/Documents/NetbeansWorkSpace/JavaSE/Jme/database/postgresql/data": 
Permission denied*/


I have followed the web page to the best of my abilities, and AFAIK, the 
postgres user owns the folder '*data*'.


I know that something is missing, where did I go wrong and how can I 
resolve this issue?



Thanks in advance.

Re: Backup Copy of a Production server.

[Ron]

On 8/7/23 00:02, KK CHN wrote:

List,

I am in need to copy a production PostgreSQL server  data( 1 TB)  to  an 
external storage( Say USB Hard Drive) and need to set up a backup server 
with this data dir.


What is the trivial method to achieve this ??

1. Is Sqldump an option at a production server ?? (  Will this affect the 
server performance  and possible slowdown of the production server ? This 
server has a high IOPS). This much size 1.2 TB will the Sqldump support ? 
Any bottlenecks ?


Whether or not there will be bottlenecks depends on how busy (CPU and disk 
load) the current server is.




2. Is copying the data directory from the production server to an external 
storage and replace the data dir  at a  backup server with same postgres 
version and replace it's data directory with this data dir copy is a 
viable option ?



# cp  -r   ./data  /media/mydb_backup  ( Does this affect the Production 
database server performance ??)   due to the copy command overhead ?



OR  doing a WAL Replication Configuration to a standby is the right method 
to achieve this ??


But you say you can't establish a network connection outside the DC.



 This is to take out the database backup outside the Datacenter and our DC 
policy won't allow us to establish a network connection outside the DC to 
a remote location for WAL replication .


If you're unsure of what Linux distro & version and Postgresql version that 
you'll be restoring the database to, then the solution is:

DB=the_database_you_want_to_backup
THREADS=
cd $PGDATA
cp -v pg_hba.conf postgresql.conf /media/mydb_backup
cd /media/mydb_backup
pg_dumpall --globals-only > globals.sql
pg_dump --format=d --verbose --jobs=$THREADS $DB &> ${DB}.log

If you're 100% positive that the system you might someday restore to is 
*exactly* the same distro & version, and Postgresql major version, then I'd 
use PgBackRest.


--
Born in Arizona, moved to Babylonia.

Backup Copy of a Production server.

[KK CHN]

List,

I am in need to copy a production PostgreSQL server  data( 1 TB)  to  an
external storage( Say USB Hard Drive) and need to set up a backup server
with this data dir.

What is the trivial method to achieve this ??

1. Is Sqldump an option at a production server ?? (  Will this affect the
server performance  and possible slowdown of the production server ? This
server has a high IOPS). This much size 1.2 TB will the Sqldump support ?
Any bottlenecks ?

2. Is copying the data directory from the production server to an external
storage and replace the data dir  at a  backup server with same postgres
version and replace it's data directory with this data dir copy is a viable
option ?


# cp  -r   ./data  /media/mydb_backup  ( Does this affect the Production
database server performance ??)   due to the copy command overhead ?


OR  doing a WAL Replication Configuration to a standby is the right method
to achieve this ??

 This is to take out the database backup outside the Datacenter and our DC
policy won't allow us to establish a network connection outside the DC to a
remote location for WAL replication .

Any hints most welcome ..

Thank you
Krishane

Re: Dropping all tables in a database

[Thorsten Glaser]

On Sun, 6 Aug 2023, H wrote:

>I am running PostgreSQL  13.11 and tried to drop all tables in a
>database without dropping the database or schema.

See:
https://evolvis.org/plugins/scmgit/cgi-bin/gitweb.cgi?p=useful-scripts/useful-scripts.git;a=tree;f=SQL;hb=HEAD

Comments welcome (especially a fix to the item still in TODO).

bye,
//mirabilos
-- 
Infrastrukturexperte • tarent solutions GmbH
Am Dickobskreuz 10, D-53121 Bonn • http://www.tarent.de/
Telephon +49 228 54881-393 • Fax: +49 228 54881-235
HRB AG Bonn 5168 • USt-ID (VAT): DE122264941
Geschäftsführer: Dr. Stefan Barth, Kai Ebenrett, Boris Esser, Alexander Steeg


/⁀\ The UTF-8 Ribbon
╲ ╱ Campaign against  Mit dem tarent-Newsletter nichts mehr verpassen:
 ╳  HTML eMail! Also, https://www.tarent.de/newsletter
╱ ╲ header encryption!

Re: Dropping all tables in a database

[David G. Johnston]

On Sun, Aug 6, 2023, 18:25 Christophe Pettus  wrote:

>
>
> > On Aug 6, 2023, at 18:17, H  wrote:
> >
> > Is there some setting I have to change in the database to have the first
> SQL statement to work or have I run into a possible bug?
>
> The first statement just generates a line of text output that contains the
> statement.  There's nothing in it that would cause that statement to be
> executed.
>
> If you want to create a statement dynamically and then execute it, you can
> do that with pl/pgSQL:
>
>
> https://www.postgresql.org/docs/current/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN
>
> Otherwise, the solution is to do as you did: write the output to a file,
> trim out any extraneous lines, and then use that as a script.
>

Or in psql execute it using the \gexec meta-command instead of a semi-colon.

David J.


>

Re: Dropping all tables in a database

[Julien Rouhaud]

On Mon, Aug 7, 2023 at 9:25 AM Christophe Pettus  wrote:
>
> > On Aug 6, 2023, at 18:17, H  wrote:
> >
> > Is there some setting I have to change in the database to have the first 
> > SQL statement to work or have I run into a possible bug?
>
> The first statement just generates a line of text output that contains the 
> statement.  There's nothing in it that would cause that statement to be 
> executed.
>
> If you want to create a statement dynamically and then execute it, you can do 
> that with pl/pgSQL:
>
> 
> https://www.postgresql.org/docs/current/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN
>
> Otherwise, the solution is to do as you did: write the output to a file, trim 
> out any extraneous lines, and then use that as a script.

The easiest solution it to simply rely on the \gexec meta-command on
psql (see https://www.postgresql.org/docs/current/app-psql.html) which
is exactly meant for that.

Re: Dropping all tables in a database

[Christophe Pettus]

> On Aug 6, 2023, at 18:17, H  wrote:
> 
> Is there some setting I have to change in the database to have the first SQL 
> statement to work or have I run into a possible bug?

The first statement just generates a line of text output that contains the 
statement.  There's nothing in it that would cause that statement to be 
executed.

If you want to create a statement dynamically and then execute it, you can do 
that with pl/pgSQL:


https://www.postgresql.org/docs/current/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

Otherwise, the solution is to do as you did: write the output to a file, trim 
out any extraneous lines, and then use that as a script.

Dropping all tables in a database

[H]

I am running PostgreSQL  13.11 and tried to drop all tables in a database 
without dropping the database or schema. After logging in as the correct user, 
the following SQL statement does not work:

SELECT 'DROP TABLE IF EXISTS "' || tablename || '" CASCADE;' FROM pg_tables 
WHERE schemaname = 'public' AND tableowner = 'xxx';

The above statement does not drop any tables, nor are there any error messages.

SELECT * FROM pg_tables;

The above shows all tables are still present in the database.

Dropping individual tables works fine but since I need to drop all tables in 
the database in a develop environment, this is not workable.

I had to resort to the following:

-- turn off headers:
\t
SELECT 'DROP TABLE IF EXISTS "' || tablename || '" CASCADE;' FROM pg_tables 
WHERE schemaname = 'public' AND tableowner = 'livraddarpaket';
\g out.tmp
\i out.tmp

The SQL statements above run fine.

Is there some setting I have to change in the database to have the first SQL 
statement to work or have I run into a possible bug?

Re: How to solve the warning?

[Jeffrey Walton]

On Sat, Aug 5, 2023 at 10:10 AM jacktby jacktby  wrote:

> How to solve the warning?

Use an explicit cast, if the types are correct.

Jeff

Re: /usr/local/pgsql is empty after successful of make

[Aramaki Zyake]

Thank you for prompt response!
I'll try it:)

On Mon, Aug 7, 2023, 01:09 Adrian Klaver  wrote:

> On 8/6/23 09:00, Aramaki Zyake wrote:
> > Hi,
> > I have a general question about PostgreSQL.
> > Yeasterday, I tried to build PostgreSQL from source code and the make
> > command succeeded, but /usr/local/pgsql directory was empty even owner
> > of pgsql was postgres user.
> >
> > I invoked following commands by postgres user.
> >
> > CFLAGS=-O0 ./configure --enable-debug
> > make  -j 2 > make.log
> >
> > I attached stdout of make command.
> >
> > This issue was 100% reproducible and there is no error message in the
> > log file and stderr was empty.
> > As far as I remember, these commands/arguments were sufficient to move
> > built artifacts into /usr/local/pgsql in the past.
> >
> > What should I do to move built artifacts into /usr/local/pgsql?
>
> Follow the instructions here:
>
> https://www.postgresql.org/docs/current/installation.html
>
> In particular:
>
> https://www.postgresql.org/docs/current/install-procedure.html
>
> 4.Installing the Files
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>

Re: /usr/local/pgsql is empty after successful of make

[Adrian Klaver]

On 8/6/23 09:00, Aramaki Zyake wrote:

Hi,
I have a general question about PostgreSQL.
Yeasterday, I tried to build PostgreSQL from source code and the make 
command succeeded, but /usr/local/pgsql directory was empty even owner 
of pgsql was postgres user.


I invoked following commands by postgres user.

CFLAGS=-O0 ./configure --enable-debug
make  -j 2 > make.log

I attached stdout of make command.

This issue was 100% reproducible and there is no error message in the 
log file and stderr was empty.
As far as I remember, these commands/arguments were sufficient to move 
built artifacts into /usr/local/pgsql in the past.


What should I do to move built artifacts into /usr/local/pgsql?


Follow the instructions here:

https://www.postgresql.org/docs/current/installation.html

In particular:

https://www.postgresql.org/docs/current/install-procedure.html

4.Installing the Files

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

Re: /usr/local/pgsql is empty after successful of make

[Tom Lane]

Aramaki Zyake  writes:
> What should I do to move built artifacts into /usr/local/pgsql?

"make install", or possibly "sudo make install" if you'd rather
the installed files be root-owned.

https://www.postgresql.org/docs/current/install-short.html

regards, tom lane

Re: /usr/local/pgsql is empty after successful of make

[Julien Rouhaud]

Hi,

On Mon, Aug 7, 2023 at 12:00 AM Aramaki Zyake  wrote:
>
> Hi,
> I have a general question about PostgreSQL.
> Yeasterday, I tried to build PostgreSQL from source code and the make command 
> succeeded, but /usr/local/pgsql directory was empty even owner of pgsql was 
> postgres user.
>
> I invoked following commands by postgres user.
>
> CFLAGS=-O0 ./configure --enable-debug
> make  -j 2 > make.log
> [...]
> As far as I remember, these commands/arguments were sufficient to move built 
> artifacts into /usr/local/pgsql in the past.

I don't think it ever behaved that way, but I might be wrong.

> What should I do to move built artifacts into /usr/local/pgsql?

the default (all) target only compiles artifacts, you need to invoke
"make install" to copy the various artifacts in the location specified
during configure (or default location), as documented in step 4 of
https://www.postgresql.org/docs/current/install-procedure.html.

/usr/local/pgsql is empty after successful of make

[Aramaki Zyake]

Hi,
I have a general question about PostgreSQL.
Yeasterday, I tried to build PostgreSQL from source code and the make
command succeeded, but /usr/local/pgsql directory was empty even owner of
pgsql was postgres user.

I invoked following commands by postgres user.

CFLAGS=-O0 ./configure --enable-debug
make  -j 2 > make.log

I attached stdout of make command.

This issue was 100% reproducible and there is no error message in the log
file and stderr was empty.
As far as I remember, these commands/arguments were sufficient to move
built artifacts into /usr/local/pgsql in the past.

What should I do to move built artifacts into /usr/local/pgsql?


make.log.gz
Description: GNU Zip compressed data