Re: PostgreSQL and local HDD

[Ron]

On 8/15/23 23:48, Olivier Gautherot wrote:


El mié, 16 ago 2023 5:39, Ron  escribió:

On 8/15/23 04:24, Olivier Gautherot wrote:
[snip]


Does the database have to be on a storage like EMC or QNAP?


Faster storage can only help but I would start by discarding
functional overhead.


Functional overhead?


I mean inefficient design, which won't be solved by changing the storage.


Ah, the normal aspects of physical design...

--
Born in Arizona, moved to Babylonia.

Re: PostgreSQL and local HDD

[Olivier Gautherot]

El mié, 16 ago 2023 5:39, Ron  escribió:

> On 8/15/23 04:24, Olivier Gautherot wrote:
>
> [snip]
>
> Does the database have to be on a storage like EMC or QNAP?
>>
>
> Faster storage can only help but I would start by discarding functional
> overhead.
>
>
> Functional overhead?
>

I mean inefficient design, which won't be solved by changing the storage.

--
Olivier Gautherot

>
>

Re: PostgreSQL and local HDD

[Ron]

On 8/15/23 02:23, Jason Long wrote:
[snip]

Does PostgreSQL have an option to increase speed?


Like a Turbo button?

--
Born in Arizona, moved to Babylonia.

Re: PostgreSQL and local HDD

[Ron]

On 8/15/23 04:24, Olivier Gautherot wrote:
[snip]


Does the database have to be on a storage like EMC or QNAP?


Faster storage can only help but I would start by discarding functional 
overhead.


Functional overhead?

--
Born in Arizona, moved to Babylonia.

Re: Cast INTEGER to BIT confusion

[Greg Sabino Mullane]

On Tue, Aug 15, 2023 at 4:49 AM [Quipsy] Markus Karg  wrote:

>
> CREATE TABLE T (c BIT);
>
> INSERT INTO T VALUES (1);
>
> -- I MUST NOT change both lines in any way as these are part of a
> third-party application!
>

That is quite the requirement! I hope that the rest of the application is
more flexible, or you might be fighting an uphill battle for a long time.

However, if the application will drive forward even after an error, you
could pre-create the table "T" the way you want (esp. "c INT"), let the
application's "CREATE TABLE" call fail, and then the subsequent insert(s)
will succeed. Other than that, there is not much that can be easily done,
as the error that is thrown happens pretty early in the process.

Cheers,
Greg

Re: Converting sql anywhere to postgres

[Peter J. Holzer]

On 2023-08-15 18:43:11 +0200, Georg H. wrote:
> update mytest.autovalues set product='pear', updated_by=DEFAULT where key=2;
 
Oh, I didn't know that worked. Obvious in hindsight, of course (I've
been using DEFAULT in VALUES(...) for ages) but I never thought of it.

hp

-- 
   _  | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| |   | h...@hjp.at |-- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |   challenge!"


signature.asc
Description: PGP signature

Re: PostgreSQL and GUI management

[Andreas Joseph Krogh]

På tirsdag 15. august 2023 kl. 22:52:48, skrev Rob Sargent <
robjsarg...@gmail.com >:
[…]
All the nice buzzwordy things: dashboards, alarm bells, point-and-click drop 
downs for config values (with tool tip descriptions of what they might do), 
coffee dispenser. Things some feel they need in a management tool.

If you need these things, I'm sure there's a budget somewhere for investing in 
available commercial tools, some already mentioned in this thread.






--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com 
www.visena.com 
 

Re: Converting sql anywhere to postgres

[Guyren Howe]

I’m fairly confident you can do this using a Rule. It would even be fairly 
simple.

Be careful, though: Rules are Postgres’ biggest potential foot gun.

Guyren G Howe
On Aug 15, 2023 at 08:05 -0700, Russell Rose | Passfield Data Systems 
, wrote:
> Hi there
>
> I am trying to convert a SQL Anywhere database to postgres. Within SQL 
> anywhere a field can have a default value of ‘last user’. This means that 
> when you perform an update on a table, if the field is not explicitly set 
> then the current user is used. So for instance if I have a field called 
> mod_user in a table, but when I do an update on the table and do not set 
> mod_user then SQL Anywhere sets the field to current_uer. I have tried to 
> replicate this using a postgres trigger in the before update. However, if I 
> do not set the value then it automatically picks up the value that was 
> already in the field. Is there a way to tell the difference between me 
> setting the value to the same as the previous value and postgres 
> automatically picking it up.
>
> If the field myfield contains the word ‘me’. Can I tell the difference 
> between:
> Update table1 set field1=’something’,myfield=’me’
> And
> Update table1 set field1=’something’
>

Re: PostgreSQL and GUI management

[Adrian Klaver]

On 8/15/23 13:52, Rob Sargent wrote:

On 8/15/23 12:57, Adrian Klaver wrote:

On 8/15/23 11:43, Rob Sargent wrote:

On 8/15/23 12:38, Adrian Klaver wrote:

On 8/15/23 08:08, Jason Long wrote:

Hello,
Does PostgreSQL have a graphical environment for management or is 
it only managed through CLI?


There are, but make your life easier and learn to use psql:

https://www.postgresql.org/docs/current/app-psql.html

It ships with Postgres and runs close to the database. Saves you the 
time tracking down the issues that the GUI transformation code 
introduce.





Thank you.



Adrian,
Much as I love it, psql is not what I would call a management tool?


Why not?



All the nice buzzwordy things: dashboards, alarm bells, point-and-click 
drop downs for config values (with tool tip descriptions of what they 
might do), coffee dispenser.  Things some feel they need in a management 
tool.


Ahh, the boss screen. Where you look like you are doing something when 
you are not.







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

Re: PostgreSQL and GUI management

[Rob Sargent]

On 8/15/23 12:57, Adrian Klaver wrote:

On 8/15/23 11:43, Rob Sargent wrote:

On 8/15/23 12:38, Adrian Klaver wrote:

On 8/15/23 08:08, Jason Long wrote:

Hello,
Does PostgreSQL have a graphical environment for management or is 
it only managed through CLI?


There are, but make your life easier and learn to use psql:

https://www.postgresql.org/docs/current/app-psql.html

It ships with Postgres and runs close to the database. Saves you the 
time tracking down the issues that the GUI transformation code 
introduce.





Thank you.



Adrian,
Much as I love it, psql is not what I would call a management tool?


Why not?



All the nice buzzwordy things: dashboards, alarm bells, point-and-click 
drop downs for config values (with tool tip descriptions of what they 
might do), coffee dispenser.  Things some feel they need in a management 
tool.

Re: PostgreSQL and GUI management

[Pavel Stehule]

út 15. 8. 2023 v 20:38 odesílatel Adrian Klaver 
napsal:

> On 8/15/23 08:08, Jason Long wrote:
> > Hello,
> > Does PostgreSQL have a graphical environment for management or is it
> > only managed through CLI?
>
> There are, but make your life easier and learn to use psql:
>
> https://www.postgresql.org/docs/current/app-psql.html
>
> It ships with Postgres and runs close to the database. Saves you the
> time tracking down the issues that the GUI transformation code introduce.
>

use with pspg https://github.com/okbob/pspg

regards

Pavel




>
> >
> >
> > Thank you.
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>
>
>

Re: PostgreSQL and GUI management

[Adrian Klaver]

On 8/15/23 11:43, Rob Sargent wrote:

On 8/15/23 12:38, Adrian Klaver wrote:

On 8/15/23 08:08, Jason Long wrote:

Hello,
Does PostgreSQL have a graphical environment for management or is it 
only managed through CLI?


There are, but make your life easier and learn to use psql:

https://www.postgresql.org/docs/current/app-psql.html

It ships with Postgres and runs close to the database. Saves you the 
time tracking down the issues that the GUI transformation code introduce.





Thank you.



Adrian,
Much as I love it, psql is not what I would call a management tool?


Why not?



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

Re: PostgreSQL and GUI management

[Andreas Joseph Krogh]

På tirsdag 15. august 2023 kl. 20:43:16, skrev Rob Sargent <
robjsarg...@gmail.com >:
[…]
Adrian, 
Much as I love it, psql is not what I would call a management tool?

We have a multi-terabyte cluster in production which we manage using psql.

Locally I use IntelliJ IDEA for development (has syntax highlight, code 
completion, introspection etc.). IDEA has a PostgreSQL plugin which is only 
commercially available, and uses the same components as DataGrip, AFAIK.






--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com 
www.visena.com 
 

Re: PostgreSQL and GUI management

[Rob Sargent]

On 8/15/23 12:38, Adrian Klaver wrote:

On 8/15/23 08:08, Jason Long wrote:

Hello,
Does PostgreSQL have a graphical environment for management or is it 
only managed through CLI?


There are, but make your life easier and learn to use psql:

https://www.postgresql.org/docs/current/app-psql.html

It ships with Postgres and runs close to the database. Saves you the 
time tracking down the issues that the GUI transformation code introduce.





Thank you.



Adrian,
Much as I love it, psql is not what I would call a management tool?

Re: PostgreSQL and GUI management

[Adrian Klaver]

On 8/15/23 08:08, Jason Long wrote:

Hello,
Does PostgreSQL have a graphical environment for management or is it 
only managed through CLI?


There are, but make your life easier and learn to use psql:

https://www.postgresql.org/docs/current/app-psql.html

It ships with Postgres and runs close to the database. Saves you the 
time tracking down the issues that the GUI transformation code introduce.





Thank you.


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

Re: Converting sql anywhere to postgres

[Russell Rose | Passfield Data Systems]

The timestamp default in Sql Anywhere is quite easy to convert with a trigger 
as I can check the old and new values and if they are the same then update. It 
is very unlikely that the application will manually set a timestamp that is 
identical to the value stored. This is not the case with a text field. I think 
I am going to have to look through the whole application to check how things 
are set and change the code as required.


Russell Rose

Developer









01404 514400



Passfield Data Systems Ltd  VAT Registration No: 673 8387 86  Company 
Registration No: 3130617  Registered address: The Globe, 165 High Street, 
Honiton, EX14 1LQ, United Kingdom



This email is sent in confidence for the addressee(s) only.  If you receive 
this communication in error, please notify us immediately and delete any 
copies.  Passfield Data Systems Ltd cannot accept responsibility for any loss 
or damage arising from any use of this e-mail or attachments. Any views 
expressed may not necessarily





From: Adrian Klaver 
Sent: Tuesday, August 15, 2023 7:19:56 PM
To: Georg H. ; Russell Rose | Passfield Data Systems 
; pgsql-general@lists.postgresql.org 

Subject: Re: Converting sql anywhere to postgres

On 8/15/23 09:43, Georg H. wrote:
> Hi,
>
> Am 15.08.2023 um 17:04 schrieb Russell Rose | Passfield Data Systems:
>>
>> Hi there
>>
>> I am trying to convert a SQL Anywhere database to postgres. Within SQL
>> anywhere a field can have a default value of ‘last user’. This means
>> that when you perform an update on a table, if the field is not
>> explicitly set then the current user is used. So for instance if I
>> have a field called mod_user in a table, but when I do an update on
>> the table and do not set mod_user then SQL Anywhere sets the field to
>> current_uer. I have tried to replicate this using a postgres trigger
>> in the before update. However, if I do not set the value then it
>> automatically picks up the value that was already in the field. Is
>> there a way to tell the difference between me setting the value to the
>> same as the previous value and postgres automatically picking it up.
>>
>> If the field myfield contains the word ‘me’. Can I tell the difference
>> between:
>>
>> Update table1 set field1=’something’,myfield=’me’
>>
>> And
>>
>> Update table1 set field1=’something’
>>
>
> maybe this is what you're looking for (without a trigger)
>
> CREATE TABLE mytest.autovalues
> (
>  key serial NOT NULL,
>  product text,
>  updated_by text DEFAULT current_user,
>  updated_at timestamp without time zone DEFAULT current_timestamp,
>  PRIMARY KEY (key)
> )
>
> TABLESPACE pg_default;
>
> ALTER TABLE IF EXISTS mytest.autovalues
>  OWNER to postgres;
>
> -- instead of current_user you may also use |session_user see
> https://www.postgresql.org/docs/current/functions-info.html|
>
> |
> |
>
> |then try:
> |
>
> |insert into mytest.autovalues (product) values ('apple') ;
> insert into mytest.autovalues (product,updated_by) values
> ('apple','justanotheruser') ;
> insert into mytest.autovalues (product,updated_by) values
> ('peach','justanotheruser') ;
> select * from mytest.autovalues;
> update mytest.autovalues set product='pear', updated_by=DEFAULT where key=2;

That is not going to catch the case the OP was interested in:

Current row:

updated_by='me'

update mytest.autovalues set product='pear', updated_by='me' where key=2

vs

update mytest.autovalues set product='pear' where key=2

In other distinguishing whether 'me' is explicitly set in the UPDATE or
whether it carried over from the OLD tuple.

> select * from mytest.autovalues;|
>
> |
> |
>
> |In case you want to "automate" the update command (not setting
> |||updated_by to DEFAULT manually/programmatically)| you may use an on
> update trigger that compares current_user/session_user with
> old.|updated_by and if they are different you could set new.updated_by
> to DEFAULT (or whatever logic fits your needs)||

Same problem as above, distinguishing between an explicitly set
update_by value and one carried over.

>
>
> kind regards
>
> Georg
>

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

Re: AW: PostgreSQL and GUI management

[Jason Long]

Hello,Thank you so much for your reply.Is it free?Has it met all your needs?

Sent from Yahoo Mail on Android 
 
  On Tue, Aug 15, 2023 at 6:40 PM, [Quipsy] Markus Karg wrote:  
 
I am using pg_admin in the browser, and it works rather fine for me.
 
-Markus
 
  
 
Von: Jason Long  
Gesendet: Dienstag, 15. August 2023 17:09
An: pgsql-general@lists.postgresql.org
Betreff: PostgreSQL and GUI management
 
  
 
Hello,
 
Does PostgreSQL have a graphical environment for management or is it only 
managed through CLI?
 
  
 
  
 
Thank you.
   

Re: Converting sql anywhere to postgres

[Adrian Klaver]

On 8/15/23 09:43, Georg H. wrote:

Hi,

Am 15.08.2023 um 17:04 schrieb Russell Rose | Passfield Data Systems:


Hi there

I am trying to convert a SQL Anywhere database to postgres. Within SQL 
anywhere a field can have a default value of ‘last user’. This means 
that when you perform an update on a table, if the field is not 
explicitly set then the current user is used. So for instance if I 
have a field called mod_user in a table, but when I do an update on 
the table and do not set mod_user then SQL Anywhere sets the field to 
current_uer. I have tried to replicate this using a postgres trigger 
in the before update. However, if I do not set the value then it 
automatically picks up the value that was already in the field. Is 
there a way to tell the difference between me setting the value to the 
same as the previous value and postgres automatically picking it up.


If the field myfield contains the word ‘me’. Can I tell the difference 
between:


Update table1 set field1=’something’,myfield=’me’

And

Update table1 set field1=’something’



maybe this is what you're looking for (without a trigger)

CREATE TABLE mytest.autovalues
(
     key serial NOT NULL,
     product text,
     updated_by text DEFAULT current_user,
     updated_at timestamp without time zone DEFAULT current_timestamp,
     PRIMARY KEY (key)
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS mytest.autovalues
     OWNER to postgres;

-- instead of current_user you may also use |session_user see 
https://www.postgresql.org/docs/current/functions-info.html|


|
|

|then try:
|

|insert into mytest.autovalues (product) values ('apple') ;
insert into mytest.autovalues (product,updated_by) values 
('apple','justanotheruser') ;
insert into mytest.autovalues (product,updated_by) values 
('peach','justanotheruser') ;

select * from mytest.autovalues;
update mytest.autovalues set product='pear', updated_by=DEFAULT where key=2;


That is not going to catch the case the OP was interested in:

Current row:

updated_by='me'

update mytest.autovalues set product='pear', updated_by='me' where key=2

vs

update mytest.autovalues set product='pear' where key=2

In other distinguishing whether 'me' is explicitly set in the UPDATE or 
whether it carried over from the OLD tuple.



select * from mytest.autovalues;|

|
|

|In case you want to "automate" the update command (not setting 
|||updated_by to DEFAULT manually/programmatically)| you may use an on 
update trigger that compares current_user/session_user with 
old.|updated_by and if they are different you could set new.updated_by 
to DEFAULT (or whatever logic fits your needs)||


Same problem as above, distinguishing between an explicitly set 
update_by value and one carried over.





kind regards

Georg



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

Re: Converting sql anywhere to postgres

[Georg H.]

Hi,

Am 15.08.2023 um 17:04 schrieb Russell Rose | Passfield Data Systems:


Hi there

I am trying to convert a SQL Anywhere database to postgres. Within SQL 
anywhere a field can have a default value of ‘last user’. This means 
that when you perform an update on a table, if the field is not 
explicitly set then the current user is used. So for instance if I 
have a field called mod_user in a table, but when I do an update on 
the table and do not set mod_user then SQL Anywhere sets the field to 
current_uer. I have tried to replicate this using a postgres trigger 
in the before update. However, if I do not set the value then it 
automatically picks up the value that was already in the field. Is 
there a way to tell the difference between me setting the value to the 
same as the previous value and postgres automatically picking it up.


If the field myfield contains the word ‘me’. Can I tell the difference 
between:


Update table1 set field1=’something’,myfield=’me’

And

Update table1 set field1=’something’



maybe this is what you're looking for (without a trigger)

CREATE TABLE mytest.autovalues
(
    key serial NOT NULL,
    product text,
    updated_by text DEFAULT current_user,
    updated_at timestamp without time zone DEFAULT current_timestamp,
    PRIMARY KEY (key)
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS mytest.autovalues
    OWNER to postgres;

-- instead of current_user you may also use |session_user see 
https://www.postgresql.org/docs/current/functions-info.html|


|
|

|then try:
|

|insert into mytest.autovalues (product) values ('apple') ;
insert into mytest.autovalues (product,updated_by) values 
('apple','justanotheruser') ;
insert into mytest.autovalues (product,updated_by) values 
('peach','justanotheruser') ;

select * from mytest.autovalues;
update mytest.autovalues set product='pear', updated_by=DEFAULT where key=2;
select * from mytest.autovalues;|

|
|

|In case you want to "automate" the update command (not setting 
|||updated_by to DEFAULT manually/programmatically)| you may use an on 
update trigger that compares current_user/session_user with 
old.|updated_by and if they are different you could set new.updated_by 
to DEFAULT (or whatever logic fits your needs)||



kind regards

Georg

Re: Converting sql anywhere to postgres

[Erik Wienhold]

> On 15/08/2023 17:04 CEST Russell Rose | Passfield Data Systems 
>  wrote:
>
> I am trying to convert a SQL Anywhere database to postgres. Within SQL
> anywhere a field can have a default value of ‘last user’. This means that
> when you perform an update on a table, if the field is not explicitly set
> then the current user is used. So for instance if I have a field called
> mod_user in a table, but when I do an update on the table and do not set
> mod_user then SQL Anywhere sets the field to current_uer. I have tried to
> replicate this using a postgres trigger in the before update. However, if
> I do not set the value then it automatically picks up the value that was
> already in the field. Is there a way to tell the difference between me
> setting the value to the same as the previous value and postgres automatically
> picking it up.
>
> If the field myfield contains the word ‘me’. Can I tell the difference
> between:
> Update table1 set field1=’something’,myfield=’me’
> And
> Update table1 set field1=’something’

Do you also have a timestamp column (let's say mod_time) that goes along with
mod_user (both updated together)?

In that case you can compare OLD.mod_time and NEW.mod_time in the BEFORE
trigger.  Only if the timestamp does not change should the trigger then assign
NEW.mod_user := current_user and NEW.mod_time := now().

Or use clock_timestamp() instead of now() if you need to handle multiple updates
of the same row in one transaction.  But this is only relevant when changing the
current user with SET ROLE during the transaction.

--
Erik

Re: Converting sql anywhere to postgres

[Adrian Klaver]

On 8/15/23 08:04, Russell Rose | Passfield Data Systems wrote:

Hi there

I am trying to convert a SQL Anywhere database to postgres. Within SQL 
anywhere a field can have a default value of ‘last user’. This means 
that when you perform an update on a table, if the field is not 
explicitly set then the current user is used. So for instance if I have 
a field called mod_user in a table, but when I do an update on the table 
and do not set mod_user then SQL Anywhere sets the field to current_uer. 
I have tried to replicate this using a postgres trigger in the before 
update. However, if I do not set the value then it automatically picks 
up the value that was already in the field. Is there a way to tell the 
difference between me setting the value to the same as the previous 
value and postgres automatically picking it up.


Not that I know of. In Postgres an UPDATE is essentially a DELETE of the 
OLD tuple and an INSERT of a new tuple. You cannot determine whether the 
same value in a given field for the OLD/NEW records was explicitly set 
the same or just carried over.




If the field myfield contains the word ‘me’. Can I tell the difference 
between:


Update table1 set field1=’something’,myfield=’me’

And

Update table1 set field1=’something’



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

Re: PostgreSQL and GUI management

[Albrecht Dreß]

Am 15.08.23 17:08 schrieb(en) Jason Long:

Hello,Does PostgreSQL have a graphical environment for management or is it only 
managed through CLI?


Have a look at DBeaver  – I use the Eclipse plug-in, but 
there are stand-alone (free) versions, too.

Hth, Albrecht.

pgpQ_nw8Yof5T.pgp
Description: PGP signature

Re: PostgreSQL and GUI management

[Pavel Stehule]

út 15. 8. 2023 v 17:16 odesílatel Pavel Stehule 
napsal:

> Hi
>
> út 15. 8. 2023 v 17:09 odesílatel Jason Long  napsal:
>
>> Hello,
>> Does PostgreSQL have a graphical environment for management or is it only
>> managed through CLI?
>>
>
>  https://wiki.postgresql.org/wiki/Community_Guide_to_PostgreSQL_GUI_Tools
>

better link  https://wiki.postgresql.org/wiki/PostgreSQL_Clients

>
> Regards
>
> Pavel
>
>
>>
>> Thank you.
>>
>

Re: PostgreSQL and GUI management

[Pavel Stehule]

Hi

út 15. 8. 2023 v 17:09 odesílatel Jason Long  napsal:

> Hello,
> Does PostgreSQL have a graphical environment for management or is it only
> managed through CLI?
>

 https://wiki.postgresql.org/wiki/Community_Guide_to_PostgreSQL_GUI_Tools

Regards

Pavel


>
> Thank you.
>

AW: PostgreSQL and GUI management

[[Quipsy] Markus Karg]

I am using pg_admin in the browser, and it works rather fine for me.
-Markus

Von: Jason Long 
Gesendet: Dienstag, 15. August 2023 17:09
An: pgsql-general@lists.postgresql.org
Betreff: PostgreSQL and GUI management

Hello,
Does PostgreSQL have a graphical environment for management or is it only 
managed through CLI?


Thank you.

PostgreSQL and GUI management

[Jason Long]

Hello,Does PostgreSQL have a graphical environment for management or is it only 
managed through CLI?


Thank you.

Converting sql anywhere to postgres

[Russell Rose | Passfield Data Systems]

Hi there

I am trying to convert a SQL Anywhere database to postgres. Within SQL anywhere 
a field can have a default value of 'last user'. This means that when you 
perform an update on a table, if the field is not explicitly set then the 
current user is used. So for instance if I have a field called mod_user in a 
table, but when I do an update on the table and do not set mod_user then SQL 
Anywhere sets the field to current_uer. I have tried to replicate this using a 
postgres trigger in the before update. However, if I do not set the value then 
it automatically picks up the value that was already in the field. Is there a 
way to tell the difference between me setting the value to the same as the 
previous value and postgres automatically picking it up.

If the field myfield contains the word 'me'. Can I tell the difference between:
Update table1 set field1='something',myfield='me'
And
Update table1 set field1='something'

Re: AW: Cast INTEGER to BIT confusion

[Erik Wienhold]

> On 15/08/2023 14:02 CEST [Quipsy] Markus Karg  wrote:
>
> I just tried out your proposal on PostgreSQL 15.3 and this is the result:
>
> ERROR:  column "c" is of type bit but expression is of type integer
> LINE 5:   INSERT INTO t VALUES (1);
> ^
> HINT:  You will need to rewrite or cast the expression.
>
> Apparently the search path is ignored?!
>
> -Ursprüngliche Nachricht-
> Von: Erik Wienhold  
> Gesendet: Dienstag, 15. August 2023 13:48
> An: [Quipsy] Markus Karg ; pgsql-general@lists.postgresql.org
> Betreff: Re: Cast INTEGER to BIT confusion
>
> You could create a custom domain if you're only interested in values 0 and 1
> and don't use bit string functions.  The search path must be changed so that
> domain bit overrides pg_catalog.bit:
> 
>   =# CREATE SCHEMA xxx;
>   =# CREATE DOMAIN xxx.bit AS int;
>   =# SET search_path = xxx, pg_catalog;
>   =# CREATE TABLE t (c bit);
>   =# INSERT INTO t VALUES (1);
>   INSERT 0 1
>
> But I would do that only if the third-party code cannot be tweaked because
> the custom domain could be confusing.  It's also prone to errors as it relies
> on a specific search path order.  Also make sure that regular users cannot
> create objects in schema xxx that would override objects in pg_catalog.

Hmm, I thought that Postgres resolves all types through the search path, but
apparently that is not the case for built-in types.  I never used this to
override built-in types so this is a surprise to me.  (And obviously I haven't
tested the search path feature before posting.)

Neither [1] or [2] mention that special (?) case or if there's a distinction
between built-in types and user-defined types.  The USAGE privilege is required
according to [2] but I was testing as superuser anyway.

[1] https://www.postgresql.org/docs/15/ddl-schemas.html
[2] 
https://www.postgresql.org/docs/15/runtime-config-client.html#GUC-SEARCH-PATH

--
Erik

Re: AW: Cast INTEGER to BIT confusion

[Erik Wienhold]

> On 15/08/2023 13:59 CEST [Quipsy] Markus Karg  wrote:
>
> Is setting the search path something that has to be done for each new
> connection / each user, or is this something static and global for the
> database?

The search path is set per connection and the initial search path can be
configured on the database and/or individual roles:

1. ALTER DATABASE mydb SET search_path = ...
2. ALTER ROLE myrole SET search_path = ...
3. ALTER ROLE myrole IN DATABASE mydb SET search_path = ...

Those three statements do not affect your current connection though.  Users can
still set a different search path after connecting.

--
Erik

Re: Query plan regression between CTE and views

[David Gilman]

I'm on PostgreSQL 15 with essentially a stock configuration.

On Tue, Aug 15, 2023 at 8:58 AM Ron  wrote:
>
> On 8/14/23 09:54, David Gilman wrote:
> > I have a query that was originally written as a handful of CTEs out of
> > convenience. It is producing a reasonable query plan because the CTE
> > materialization was kicking in at an appropriate place. The CTEs
> > aren't totally linear. The graph looks like this, where A, B, C and D
> > are CTEs, and B -> A means B selects from A. In Graphviz format:
> >
> > G {
> > B -> A;
> > C -> A;
> > C -> B;
> > D -> C;
> > }
> >
> > Out of curiosity I tried turning the query into a series of views and
> > ran that query. The query plan is vastly different, there is no
> > materialization and it runs much slower.
> >
> > My question is: is this a valid bug? I am not sure if I should expect
> > the view version to find a way to materialize and produce a comparable
> > query plan. Also, making a minimal test case is going to take a bit
> > and I don't want to start unless this smells like a genuine bug.
>
>
> What version of Postgresql?
>
> (Also, back before, I think, v12, CTEs were optimizer fences.  You were
> better using views or sub-queries.)
>
> --
> Born in Arizona, moved to Babylonia.
>
>
>
>


-- 
David Gilman
:DG<

AW: Cast INTEGER to BIT confusion

[[Quipsy] Markus Karg]

Erik,

I just tried out your proposal on PostgreSQL 15.3 and this is the result:

ERROR:  column "c" is of type bit but expression is of type integer
LINE 5:   INSERT INTO t VALUES (1);
^
HINT:  You will need to rewrite or cast the expression.

Apparently the search path is ignored?!

-Markus

-Ursprüngliche Nachricht-
Von: Erik Wienhold  
Gesendet: Dienstag, 15. August 2023 13:48
An: [Quipsy] Markus Karg ; pgsql-general@lists.postgresql.org
Betreff: Re: Cast INTEGER to BIT confusion

> On 15/08/2023 10:49 CEST [Quipsy] Markus Karg  wrote:
>
> Hello PostgreSQL Community,
>
> I like to store just a single bit but that can be either 1 or 0, so I 
> tried to do this:
>
> CREATE TABLE T (c BIT);
> INSERT INTO T VALUES (1);
> -- I MUST NOT change both lines in any way as these are part of a third-party 
> application!
>
> Unfortunately this tells me:
>
> column "c" is of type bit but expression is of type integer
>
> So I logged in as a cluster admin and I tried this:
>
> CREATE CAST (integer AS bit) WITH INOUT AS IMPLICIT;
>
> Unfortunately that tells me:
>
> cast from type integer to type bit already exists
>
> This is confusing! Apparently PostgreSQL 15.3 comes with the needed 
> cast out-of-the-box but it does not apply it? This is confusing!
>
> What is my fault?

The built-in cast is explicit (castcontext = 'e'):

=# SELECT * FROM pg_cast WHERE castsource = 'int'::regtype AND 
casttarget = 'bit'::regtype;
  oid  | castsource | casttarget | castfunc | castcontext | castmethod
---+++--+-+
 10186 | 23 |   1560 | 1683 | e   | f
(1 row)

It's not possible to drop that cast and replace it with a custom one:

=# DROP CAST (int AS bit);
ERROR:  cannot drop cast from integer to bit because it is required by 
the database system

You could create a custom domain if you're only interested in values 0 and 1 
and don't use bit string functions.  The search path must be changed so that 
domain bit overrides pg_catalog.bit:

=# CREATE SCHEMA xxx;
=# CREATE DOMAIN xxx.bit AS int;
=# SET search_path = xxx, pg_catalog;
=# CREATE TABLE t (c bit);
=# INSERT INTO t VALUES (1);
INSERT 0 1

But I would do that only if the third-party code cannot be tweaked because the 
custom domain could be confusing.  It's also prone to errors as it relies on a 
specific search path order.  Also make sure that regular users cannot create 
objects in schema xxx that would override objects in pg_catalog.

--
Erik

AW: Cast INTEGER to BIT confusion

[[Quipsy] Markus Karg]

Thank you, Erik! Prefixing the search path in fact looks very interesting, and 
I think in this particular application it is a safe (and the only) solution.

Is setting the search path something that has to be done for each new 
connection / each user, or is this something static and global for the database?

Thanks a lot!
-Markus


-Ursprüngliche Nachricht-
Von: Erik Wienhold  
Gesendet: Dienstag, 15. August 2023 13:48
An: [Quipsy] Markus Karg ; pgsql-general@lists.postgresql.org
Betreff: Re: Cast INTEGER to BIT confusion

> On 15/08/2023 10:49 CEST [Quipsy] Markus Karg  wrote:
>
> Hello PostgreSQL Community,
>
> I like to store just a single bit but that can be either 1 or 0, so I 
> tried to do this:
>
> CREATE TABLE T (c BIT);
> INSERT INTO T VALUES (1);
> -- I MUST NOT change both lines in any way as these are part of a third-party 
> application!
>
> Unfortunately this tells me:
>
> column "c" is of type bit but expression is of type integer
>
> So I logged in as a cluster admin and I tried this:
>
> CREATE CAST (integer AS bit) WITH INOUT AS IMPLICIT;
>
> Unfortunately that tells me:
>
> cast from type integer to type bit already exists
>
> This is confusing! Apparently PostgreSQL 15.3 comes with the needed 
> cast out-of-the-box but it does not apply it? This is confusing!
>
> What is my fault?

The built-in cast is explicit (castcontext = 'e'):

=# SELECT * FROM pg_cast WHERE castsource = 'int'::regtype AND 
casttarget = 'bit'::regtype;
  oid  | castsource | casttarget | castfunc | castcontext | castmethod
---+++--+-+
 10186 | 23 |   1560 | 1683 | e   | f
(1 row)

It's not possible to drop that cast and replace it with a custom one:

=# DROP CAST (int AS bit);
ERROR:  cannot drop cast from integer to bit because it is required by 
the database system

You could create a custom domain if you're only interested in values 0 and 1 
and don't use bit string functions.  The search path must be changed so that 
domain bit overrides pg_catalog.bit:

=# CREATE SCHEMA xxx;
=# CREATE DOMAIN xxx.bit AS int;
=# SET search_path = xxx, pg_catalog;
=# CREATE TABLE t (c bit);
=# INSERT INTO t VALUES (1);
INSERT 0 1

But I would do that only if the third-party code cannot be tweaked because the 
custom domain could be confusing.  It's also prone to errors as it relies on a 
specific search path order.  Also make sure that regular users cannot create 
objects in schema xxx that would override objects in pg_catalog.

--
Erik

Re: Cast INTEGER to BIT confusion

[Erik Wienhold]

> On 15/08/2023 10:49 CEST [Quipsy] Markus Karg  wrote:
>
> Hello PostgreSQL Community,
>
> I like to store just a single bit but that can be either 1 or 0, so I tried
> to do this:
>
> CREATE TABLE T (c BIT);
> INSERT INTO T VALUES (1);
> -- I MUST NOT change both lines in any way as these are part of a third-party 
> application!
>
> Unfortunately this tells me:
>
> column "c" is of type bit but expression is of type integer
>
> So I logged in as a cluster admin and I tried this:
>
> CREATE CAST (integer AS bit) WITH INOUT AS IMPLICIT;
>
> Unfortunately that tells me:
>
> cast from type integer to type bit already exists
>
> This is confusing! Apparently PostgreSQL 15.3 comes with the needed cast
> out-of-the-box but it does not apply it? This is confusing!
>
> What is my fault?

The built-in cast is explicit (castcontext = 'e'):

=# SELECT * FROM pg_cast WHERE castsource = 'int'::regtype AND 
casttarget = 'bit'::regtype;
  oid  | castsource | casttarget | castfunc | castcontext | castmethod
---+++--+-+
 10186 | 23 |   1560 | 1683 | e   | f
(1 row)

It's not possible to drop that cast and replace it with a custom one:

=# DROP CAST (int AS bit);
ERROR:  cannot drop cast from integer to bit because it is required by 
the database system

You could create a custom domain if you're only interested in values 0 and 1
and don't use bit string functions.  The search path must be changed so that
domain bit overrides pg_catalog.bit:

=# CREATE SCHEMA xxx;
=# CREATE DOMAIN xxx.bit AS int;
=# SET search_path = xxx, pg_catalog;
=# CREATE TABLE t (c bit);
=# INSERT INTO t VALUES (1);
INSERT 0 1

But I would do that only if the third-party code cannot be tweaked because the
custom domain could be confusing.  It's also prone to errors as it relies on a
specific search path order.  Also make sure that regular users cannot create
objects in schema xxx that would override objects in pg_catalog.

--
Erik

Re: PostgreSQL and local HDD

[Olivier Gautherot]

Hi Jason

On Tue, Aug 15, 2023 at 9:23 AM Jason Long  wrote:

> Hello,
> Why is the PostgreSQL database slow when it is on the server's hard drive?
> My servers are HPE ProLiant DL380p G8 and HPE ProLiant DL380 G9.
>

Can you be more specific in your question? Reasons for the server would be
"slow" can be many and can be addressed in many ways. So additional
information would be welcome:
- Size of the database
- Number of rows (and their number of columns, size...) in the tables
- Indices and other optimization tricks
- The query itself
- ... and other server configuration parameters

Does the database have to be on a storage like EMC or QNAP?
>

Faster storage can only help but I would start by discarding functional
overhead.

Does PostgreSQL have an option to increase speed?
>

It is usually achieved by reducing the number of rows you pull from the
disk.
Try the command EXPLAIN (
https://www.postgresql.org/docs/current/sql-explain.html )  to see where
the server is wasting time.


> Thank you.
>

--
Olivier Gautherot

Cast INTEGER to BIT confusion

[[Quipsy] Markus Karg]

Hello PostgreSQL Community,

I have 25+ years of experience with some other RDBMS, but I am a PostgreSQL 
starter, so I assume the following is rather a simple beginner's question...:

I like to store just a single bit but that can be either 1 or 0, so I tried to 
do this:

CREATE TABLE T (c BIT);
INSERT INTO T VALUES (1);
-- I MUST NOT change both lines in any way as these are part of a third-party 
application!

Unfortunately this tells me:

column "c" is of type bit but expression is of type integer

So I logged in as a cluster admin and I tried this:

CREATE CAST (integer AS bit) WITH INOUT AS IMPLICIT;

Unfortunately that tells me:

cast from type integer to type bit already exists

This is confusing! Apparently PostgreSQL 15.3 comes with the needed cast 
out-of-the-box but it does not apply it? This is confusing!

What is my fault?

Thanks!
-Markus

PostgreSQL and local HDD

[Jason Long]

Hello,
Why is the PostgreSQL database slow when it is on the server's hard drive? My 
servers are HPE ProLiant DL380p G8 and HPE ProLiant DL380 G9.

Does the database have to be on a storage like EMC or QNAP?

Does PostgreSQL have an option to increase speed?


Thank you.