Re: Converting sql anywhere to postgres

[Adrian Klaver]

On 8/16/23 14:16, Rob Sargent wrote:





I have just had a quick look at rules and I am not sure how it can be done. 
Rules still use the concept of NEW and OLD. If my original row has 'myfield' 
set to 'me' then I don't think I can tell the difference between:

Update mytable set afield='something'
and
Update mytable set afield='something',myfield='me'

Within the rule I think NEW.myfield will be set to 'me' in both cases. Please 
can you explain how I can tell the difference between the two update statements


If the original value in the user column is "me", what is the difference between "set 
other_column = some_value, user = 'me'" and "set other_column = some_value" at the business 
level?


Affirmation that the user updating the record explicitly set the user value.

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


Agreed.  But at the end of the day, the difference is what exactly?  Wouldn't 
auditing (short of sql logging) say “no change” with respect to “me” column?


That is above my pay grade, that is for the OP to elaborate on. I was 
just saying that the Postgres server could not make that distinction 
with the information provided.




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

Re: Converting sql anywhere to postgres

[Rob Sargent]

> On Aug 16, 2023, at 1:35 PM, Adrian Klaver  wrote:
> 
> On 8/16/23 12:01, Rob Sargent wrote:
>> On 8/16/23 12:30, Guyren Howe wrote:
>>> For some reason, I was thinking the rule could see just the fields from the 
>>> command, but you’re right; a rule won’t work. Sorry.
>>> 
>>> Guyren G Howe
>>> On Aug 15, 2023 at 23:22 -0700, Russell Rose | Passfield Data Systems 
>>> , wrote:
 I have just had a quick look at rules and I am not sure how it can be 
 done. Rules still use the concept of NEW and OLD. If my original row has 
 'myfield' set to 'me' then I don't think I can tell the difference between:
 
 Update mytable set afield='something'
 and
 Update mytable set afield='something',myfield='me'
 
 Within the rule I think NEW.myfield will be set to 'me' in both cases. 
 Please can you explain how I can tell the difference between the two 
 update statements
 
>> If the original value in the user column is "me", what is the difference 
>> between "set other_column = some_value, user = 'me'" and "set other_column = 
>> some_value" at the business level?
> 
> Affirmation that the user updating the record explicitly set the user value.
> 
> -- 
> Adrian Klaver
> adrian.kla...@aklaver.com
> 
Agreed.  But at the end of the day, the difference is what exactly?  Wouldn't 
auditing (short of sql logging) say “no change” with respect to “me” column?

This then is a client issue, no?  There has to be two paths in the client code, 
one which generates an update without “me” and one which includes “me" and the 
second path does not take in to account current value.  If it’s worth the 
effort the latter code path needs to be cognizant of the current state of the 
record (“me” column).  

Re: Creating a TABLESPACE

[Ray O'Donnell]

On 16/08/2023 21:24, Ron wrote:
Speakers of English as a second language are to be tolerated and 
assisted, not mocked and insulted.


+1

I actually found it rather amusing. :-)

Ray.




On 8/16/23 12:27, Amn Ojee Uw wrote:


Sorry, did not mean it that way.
Thanks for the advice.

On 8/16/23 11:37 a.m., Ron wrote:
Friendly tip: whatever else you do, don't write "Hello children!" in 
English to adults.  It's *highly insulting*.


On 8/16/23 05:10, Amn Ojee Uw wrote:


Hello children!



--
Born in Arizona, moved to Babylonia.


--
Born in Arizona, moved to Babylonia.


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

Re: Creating a TABLESPACE

[Ron]

Speakers of English as a second language are to be tolerated and assisted, 
not mocked and insulted.


On 8/16/23 12:27, Amn Ojee Uw wrote:


Sorry, did not mean it that way.
Thanks for the advice.

On 8/16/23 11:37 a.m., Ron wrote:
Friendly tip: whatever else you do, don't write "Hello children!" in 
English to adults.  It's *highly insulting*.


On 8/16/23 05:10, Amn Ojee Uw wrote:


Hello children!



--
Born in Arizona, moved to Babylonia.


--
Born in Arizona, moved to Babylonia.

Re: Converting sql anywhere to postgres

[Adrian Klaver]

On 8/16/23 12:01, Rob Sargent wrote:

On 8/16/23 12:30, Guyren Howe wrote:
For some reason, I was thinking the rule could see just the fields 
from the command, but you’re right; a rule won’t work. Sorry.


Guyren G Howe
On Aug 15, 2023 at 23:22 -0700, Russell Rose | Passfield Data Systems 
, wrote:
I have just had a quick look at rules and I am not sure how it can be 
done. Rules still use the concept of NEW and OLD. If my original row 
has 'myfield' set to 'me' then I don't think I can tell the 
difference between:


Update mytable set afield='something'
and
Update mytable set afield='something',myfield='me'

Within the rule I think NEW.myfield will be set to 'me' in both 
cases. Please can you explain how I can tell the difference between 
the two update statements


If the original value in the user column is "me", what is the difference 
between "set other_column = some_value, user = 'me'" and "set 
other_column = some_value" at the business level?


Affirmation that the user updating the record explicitly set the user value.

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

Re: Converting sql anywhere to postgres

[Rob Sargent]

On 8/16/23 12:30, Guyren Howe wrote:
For some reason, I was thinking the rule could see just the fields 
from the command, but you’re right; a rule won’t work. Sorry.


Guyren G Howe
On Aug 15, 2023 at 23:22 -0700, Russell Rose | Passfield Data Systems 
, wrote:
I have just had a quick look at rules and I am not sure how it can be 
done. Rules still use the concept of NEW and OLD. If my original row 
has 'myfield' set to 'me' then I don't think I can tell the 
difference between:


Update mytable set afield='something'
and
Update mytable set afield='something',myfield='me'

Within the rule I think NEW.myfield will be set to 'me' in both 
cases. Please can you explain how I can tell the difference between 
the two update statements


If the original value in the user column is "me", what is the difference 
between "set other_column = some_value, user = 'me'" and "set 
other_column = some_value" at the business level?

Re: Converting sql anywhere to postgres

[Guyren Howe]

For some reason, I was thinking the rule could see just the fields from the 
command, but you’re right; a rule won’t work. Sorry.

Guyren G Howe
On Aug 15, 2023 at 23:22 -0700, Russell Rose | Passfield Data Systems 
, wrote:
> I have just had a quick look at rules and I am not sure how it can be done. 
> Rules still use the concept of NEW and OLD. If my original row has 'myfield' 
> set to 'me' then I don't think I can tell the difference between:
>
> Update mytable set afield='something'
> and
> Update mytable set afield='something',myfield='me'
>
> Within the rule I think NEW.myfield will be set to 'me' in both cases. Please 
> can you explain how I can tell the difference between the two update 
> statements
>

Re: Creating a TABLESPACE

[Amn Ojee Uw]

Sorry, did not mean it that way.
Thanks for the advice.

On 8/16/23 11:37 a.m., Ron wrote:
Friendly tip: whatever else you do, don't write "Hello children!" in 
English to adults.  It's *highly insulting*.


On 8/16/23 05:10, Amn Ojee Uw wrote:


Hello children!



--
Born in Arizona, moved to Babylonia.

Re: Creating a TABLESPACE

[Amn Ojee Uw]

Yes!
Thank you Mateusz.

On 8/16/23 6:43 a.m., Mateusz Henicz wrote:

Hey,
You are using "WITH tablespace_option" wrong.

Try it like this:

postgres=# CREATE TABLESPACE jme_tablespace OWNER jamiil LOCATION 
'/database' WITH 
(seq_page_cost=1.0,random_page_cost=4.0,effective_io_concurrency=1);

CREATE TABLESPACE

Cheers,
Mateusz

śr., 16 sie 2023 o 12:10 Amn Ojee Uw  napisał(a):

Hello children!

I am learning how to create a tablespace, and AFAIK this are the
steps to follow.

First step is to create the directory where the database files
will be created, for that I am using the root directory
'/database'. This directory is be owned by the postgres group.

Example :
sudo mkdir /database
sudo chown postgres:postgres /database

I read the following documentation
1.) TABLESPACE


2.) seq_page_cost



3.) effective_io_concurrency



4.) maintenance_io_concurrency



5.) Tablespaces


x.) effective_io_concurrency


I then login PostgreSQL as so : sudo -u postgres psql.

Note : _/*jamiil*/_ is an actual account, it does exist!

I proceed to create the 'TABLESPACE' using the default values in
PostgreSQL

CREATE TABLESPACE jme_tablespace OWNER jamiil LOCATION '/database'
WITH tablespace_option = seq_page_cost(1.0), random_page_cost
(4.0), effective_io_concurrency (1), maintenance_io_concurrency (10);

But then I get this error message:
*ERROR:  syntax error at or near "tablespace_option"**
**LINE 1: ...tablespace OWNER jamiil LOCATION '/database' WITH
tablespace...*

What did I go wrong?

This exercise is not imperative, but as a student of PostgreSQL
I'd like to try all there is to learn about PostgreSQL.

Thanks in advance for your time and interest.

Re: Creating a TABLESPACE

[Ron]

Friendly tip: whatever else you do, don't write "Hello children!" in English 
to adults.  It's *highly insulting*.


On 8/16/23 05:10, Amn Ojee Uw wrote:


Hello children!



--
Born in Arizona, moved to Babylonia.

Re: PostgreSQL and local HDD

[Ron]

On 8/16/23 03:40, Andreas Joseph Krogh wrote:

På onsdag 16. august 2023 kl. 05:40:40, skrev Ron :

On 8/15/23 02:23, Jason Long wrote:
[snip]
> Does PostgreSQL have an option to increase speed?

Like a Turbo button?

It actually has that, but you'll have to sacrifice some safety.



lol... we /never/ push /that/ turbo button.

--
Born in Arizona, moved to Babylonia.

Re: Creating a TABLESPACE

[Mateusz Henicz]

Hey,
You are using "WITH tablespace_option" wrong.

Try it like this:

postgres=# CREATE TABLESPACE jme_tablespace OWNER jamiil LOCATION
'/database' WITH
(seq_page_cost=1.0,random_page_cost=4.0,effective_io_concurrency=1);
CREATE TABLESPACE

Cheers,
Mateusz

śr., 16 sie 2023 o 12:10 Amn Ojee Uw  napisał(a):

> Hello children!
>
> I am learning how to create a tablespace, and AFAIK this are the steps to
> follow.
>
> First step is to create the directory where the database files will be
> created, for that I am using the root directory '/database'. This directory
> is be owned by the postgres group.
>
> Example :
> sudo mkdir /database
> sudo chown postgres:postgres /database
>
> I read the following documentation
> 1.) TABLESPACE
> 
>
> 2.) seq_page_cost
> 
>
> 3.) effective_io_concurrency
> 
>
> 4.) maintenance_io_concurrency
> 
>
> 5.) Tablespaces
> 
>
> x.) effective_io_concurrency
> 
>
> I then login PostgreSQL as so : sudo -u postgres psql.
>
> Note : *jamiil* is an actual account, it does exist!
>
> I proceed to create the 'TABLESPACE' using the default values in PostgreSQL
>
> CREATE TABLESPACE jme_tablespace OWNER jamiil LOCATION '/database' WITH
> tablespace_option = seq_page_cost(1.0), random_page_cost (4.0),
> effective_io_concurrency (1), maintenance_io_concurrency (10);
>
> But then I get this error message:
> *ERROR:  syntax error at or near "tablespace_option"*
> *LINE 1: ...tablespace OWNER jamiil LOCATION '/database' WITH
> tablespace...*
>
> What did I go wrong?
>
> This exercise is not imperative, but as a student of PostgreSQL I'd like
> to try all there is to learn about PostgreSQL.
>
> Thanks in advance for your time and interest.
>

Re: Query regarding managing Replication

[Ashok Patil]

Hello,

I have one query regarding client/server authentication using certificate
in postgres.

I am able to establish client server connection with OpenSSL certificate
creation.

We can create server and root certificates using OpenSSL and keep them in
data directory.
We need to update field in postgresql.conf (example ssl = ON, ssl_cert_file
= "Server.crt", ssl_key_file = "Server.key", also for client we need to
create certificate and key and need to update it in user directory.

My query is, instead of using a physical certificate from a
particular directory, can we use a certificate from the Windows certificate
store for both server and client? Server will refer to the installed
certificate from the server windows certificate store and the client will
use the client windows certificate store.

Please let me know if such a thing we can use. if yes , please share some
references.

Thanks and Regards,
Ashok

On Mon, Jul 10, 2023 at 1:32 PM Julien Rouhaud  wrote:

> On Mon, Jul 10, 2023 at 3:41 PM Ashok Patil  wrote:
>
>> Hello Julien,
>>
>> I need some more help regarding replication.
>>
>> Where Primary server can switch to Standby server role and stand by
>> server can switch to Primary server role.
>> [image: image.png]
>> In our case we maintain two servers Server1 and Server2.. Server1 will be
>> active and Server2 will be passive.
>> In case when Server1 is crashed Server2 will be active in result it
>> starts Keyclock instance and keyclock will connect with Standby postgres
>> server.
>>
>> As per current replication steps, we found that Standby server will
>> always be in read only mode. we want to switch it to r/w mode and primary
>> to read only mode.
>>
>> or even it is ok both primary and stand by will always be in read-write
>> mode. but both should replicate data.
>>
>> I searched so many option on internet but did not found exact one.. Will
>> you please propose me some good solutions..
>>
>> Proper steps should be appreciable..
>>
>
> I don't know what KeyClock is, but I'm assuming that what you're looking
> for is a High Availability solution, not how to setup replication, as
> postgres itself only provides way to perform specific actions (promote a
> standby to primary, build a new standby...) and third-party tools adds the
> necessary logic for something more fancy.  For general overview or how to
> perform a failover you can look at the documentation at
> https://www.postgresql.org/docs/current/high-availability.html.  If
> you're looking for a HA solution, I would recommend looking into Patroni:
> https://patroni.readthedocs.io/en/latest/
>

Creating a TABLESPACE

[Amn Ojee Uw]

Hello children!

I am learning how to create a tablespace, and AFAIK this are the steps 
to follow.


First step is to create the directory where the database files will be 
created, for that I am using the root directory '/database'. This 
directory is be owned by the postgres group.


Example :
sudo mkdir /database
sudo chown postgres:postgres /database

I read the following documentation
1.) TABLESPACE 



2.) seq_page_cost 



3.) effective_io_concurrency 



4.) maintenance_io_concurrency 



5.) Tablespaces 



x.) effective_io_concurrency 



I then login PostgreSQL as so : sudo -u postgres psql.

Note : _/*jamiil*/_ is an actual account, it does exist!

I proceed to create the 'TABLESPACE' using the default values in PostgreSQL

CREATE TABLESPACE jme_tablespace OWNER jamiil LOCATION '/database' WITH 
tablespace_option = seq_page_cost(1.0), random_page_cost (4.0), 
effective_io_concurrency (1), maintenance_io_concurrency (10);


But then I get this error message:
*ERROR:  syntax error at or near "tablespace_option"**
**LINE 1: ...tablespace OWNER jamiil LOCATION '/database' WITH 
tablespace...*


What did I go wrong?

This exercise is not imperative, but as a student of PostgreSQL I'd like 
to try all there is to learn about PostgreSQL.


Thanks in advance for your time and interest.

Re: A Good Beginner's Book

[Amn Ojee Uw]

Thanks for the advice.
I just ordered it from Amazon.

On 8/12/23 6:59 p.m., Anthony DeBarros wrote:

Hi,

On Sat, Aug 12, 2023 at 6:23 PM Miles Elam  
wrote:


> On 8/12/23 9:02 a.m., Amn Ojee Uw wrote:
>
> Is there a book to be recommended for PostgreSQL beginners?


I’m the author of Practical SQL from No Starch Press. My book combines 
an intro to SQL with lessons on data analysis. Learn more at 
https://practicalsql.com

Re: PostgreSQL and local HDD

[Andreas Joseph Krogh]

På onsdag 16. august 2023 kl. 05:40:40, skrev Ron mailto:ronljohnso...@gmail.com>>:
On 8/15/23 02:23, Jason Long wrote:
[snip]
> Does PostgreSQL have an option to increase speed?

Like a Turbo button?
It actually has that, but you'll have to sacrifice some safety.






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

Re: Converting sql anywhere to postgres

[Russell Rose | Passfield Data Systems]

I have just had a quick look at rules and I am not sure how it can be done. 
Rules still use the concept of NEW and OLD. If my original row has 'myfield' 
set to 'me' then I don't think I can tell the difference between:

Update mytable set afield='something'
and
Update mytable set afield='something',myfield='me'

Within the rule I think NEW.myfield will be set to 'me' in both cases. Please 
can you explain how I can tell the difference between the two update statements

Re: PostgreSQL and local HDD

[Olivier Gautherot]

El mié, 16 ago 2023 6:54, Ron  escribió:

> 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...
>

... and considerung that the OP did not quantify what "slow" means and what
his reference/expectation is.

Let's wait for the OP to give more background.

>