Re: Was my question inappropriate for postgres?

[Adrian Klaver]

On 7/24/22 18:14, Mladen Gogala wrote:

Answers in-line:




https://dbwhisperer.wordpress.com/2020/11/21/pyodbc-fast_executemany-and-oracle-rdbms/

MS SQL ODBC 1.7 and Oracle Instant Client 19.9 ODBC drivers and newer do 
support "fast_executemany". PostgreSQL ODBC 13 does not. Here is the table:


https://github.com/mkleehammer/pyodbc/wiki/fast_executemany-support-for-various-ODBC-drivers




As to below, really?  If you don't want to answer the question then 
don't.


Boys just wanna have fun. This is a shameless plug of the famous Cindy 
Lauper's song, but you get the picture, don't you? To my credit, I 
didn't answer the 1st one. Tonight, I've been socializing with Sam 
Adams, so I replied. When someone who doesn't know how to properly write 
"O365" asks about "how suitable ODBC driver is", then I feel entitled to 
little fun.


Now, since you don't appear to be a fan of my particular variety of 
humor, can you possibly translate the question and explain to me what a 
"suitable driver" is and what is M365? If you manage to prove to me that 


1) Looking at the email of the OP would provide information that there 
might be a language barrier involved. And I who have been known to be 
less then clear in the one language I know, attempt to be forgiving of 
those that are trying to get a thought across in a foreign language.


2) Suitable driver is just that a driver that will suit the needs of 
working on Windows 10 with MS Office/Office/Microsoft 365. I am not sure 
how that could be made any clearer.


3) "...compatible to Microsoft's M365." I who am not a Windows user, 
knew what the OP meant. And if you go here:


https://en.wikipedia.org/wiki/Microsoft_365#As_%22Microsoft_365%22

You will see:

"On March 30, 2020, Microsoft announced that the consumer plans of 
Office 365 would be rebranded as "Microsoft 365" (a brand also used by 
Microsoft for an enterprise subscription bundle of Windows, Office 365, 
and security services) on April 21, 2020, succeeding existing consumer 
plans of Office 365."


So M365 is actually more correct then 0365.




the question makes sense, I will apologize to the OP and offer you a 
pint of Sam Adams as an apology for my bad behavior. On the other hand, 
if the question doesn't make sense, I will gladly accept the same from you.


--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com




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

Re: Was my question inappropriate for postgres?

[Mladen Gogala]

Answers in-line:

On 7/24/22 20:43, Adrian Klaver wrote:
Other parts of Office also use database connectivity such   as Access 
or Word.



Also, I find variety in the odbc driver.


I don't. ODBC drivers implement the same protocol when communicating 
with the database and they all work the same. Here is what I have:


Hmm, I wonder why they have versions then?


Good question. One part of the story are package versions, bug fixes and 
such. However, I think that your question was about the versions of the 
ODBC protocol. This may answer it:


https://en.wikipedia.org/wiki/Open_Database_Connectivity


 Version history

Version history:^[22] 



 * 1.0: released in September 1992^[23]
   
 * 2.0: c. 1994
 * 2.5
 * 3.0: c. 1995, John Goodson of Intersolv and Frank Pellow and Paul
   Cotton of IBM provided significant input to ODBC 3.0^[24]
   
 * 3.5: c. 1997
 * 3.8: c. 2009, with Windows 7^[25]
   


 * 4.0: Development announced June 2016^[26]
   

   with first implementation with SQL Server 2017 released Sep 2017 and
   additional desktop drivers late 2018 ^[27]
   
   final spec on Github 

By the way, version 4 of the protocol supports "fast_executemany":

https://dbwhisperer.wordpress.com/2020/11/21/pyodbc-fast_executemany-and-oracle-rdbms/

MS SQL ODBC 1.7 and Oracle Instant Client 19.9 ODBC drivers and newer do 
support "fast_executemany". PostgreSQL ODBC 13 does not. Here is the table:


https://github.com/mkleehammer/pyodbc/wiki/fast_executemany-support-for-various-ODBC-drivers




As to below, really?  If you don't want to answer the question then 
don't.


Boys just wanna have fun. This is a shameless plug of the famous Cindy 
Lauper's song, but you get the picture, don't you? To my credit, I 
didn't answer the 1st one. Tonight, I've been socializing with Sam 
Adams, so I replied. When someone who doesn't know how to properly write 
"O365" asks about "how suitable ODBC driver is", then I feel entitled to 
little fun.


Now, since you don't appear to be a fan of my particular variety of 
humor, can you possibly translate the question and explain to me what a 
"suitable driver" is and what is M365? If you manage to prove to me that 
the question makes sense, I will apologize to the OP and offer you a 
pint of Sam Adams as an apology for my bad behavior. On the other hand, 
if the question doesn't make sense, I will gladly accept the same from you.


--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

Re: Was my question inappropriate for postgres?

[Adrian Klaver]

On 7/24/22 17:32, Gavin Flower wrote:

On 25/07/22 12:19, Adrian Klaver wrote:

On 7/24/22 17:15, Gavin Flower wrote:

On 25/07/22 11:56, Taka Taka wrote:

Hello.


I suspect that most people do not know what 'M365' is, would be good 
to explain.  Am curious as to what 'M365' is!


New branding and pricing(subscription) of MS Office.

Microsoft 365
https://www.microsoft.com/en-us/microsoft-365


[...]

Thanks,  I use LibreOffice which is free and easier to use -- it runs on 
many O/S's including those from Micosoft.

https://www.libreoffice.org/



That is what I use also. Unfortunately if you are in a true blue MS shop 
LibreOffice will only take you so far at which point you will need MS 
365 to get things done. FYI, MS 365 is MS looking at Apple and realizing 
that they needed to convert to having users generate a continuous 
revenue stream instead of having them hang on to site licensed copy for 
years.


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

Re: Was my question inappropriate for postgres?

[Adrian Klaver]

On 7/24/22 17:27, Mladen Gogala wrote:

On 7/24/22 19:56, Taka Taka wrote:

Hello.

I posted question bellow.



And? No takers? I wonder why. Maybe the question is unclear? This is 
really surprising :)


The question was straight forward and clear.




Hello.
I would like to know if psqlodbc_13_02 is compatible with M365.
Also, could you please tell me which of the psqlodbc_13_02 would be
suitable to Windows 10 64-bit?


What is M365? Is it a part of Microsoft Office 365, aka "O365"? Is it 
related to M-16? What di you mean by "driver suitable for Windows 10"? 
If the driver can be installed and configured by the MS ODBC driver 
administrator, then I guess it's suitable. Make sure the driver you use 
is mauve, they take the least RAM


The ODBC lacks the version matrix the JDBC site:

https://jdbc.postgresql.org/download.html

has so I think is appropriate to verify version compatibility. Though as 
I mentioned it probably a low work task to just try it.




Was my question inappropriate?
I wanted to know if the pdbc driver above would be compatible to 
Microsoft's M365.

What part of Office 365 do you want the compatibility with? Excel?


Other parts of Office also use database connectivity such   as Access or 
Word.



Also, I find variety in the odbc driver.


I don't. ODBC drivers implement the same protocol when communicating 
with the database and they all work the same. Here is what I have:


Hmm, I wonder why they have versions then?

As to below, really?  If you don't want to answer the question then don't.



The string "f36" in the PostgreSQL ODBC package name denotes a very 
special version of Windows, more advanced than even Windows 11. I was 
able to connect all 3 of the drivers above to LibreOffice and use them 
to query the underlying database. I was able to do the same on my 
Windows 8.1, with all 3 of those drivers.


Is it possible to know which of the driver is suitable to Windows 10 
64-bit?


Suitable how? What exactly do you want to do? What does the word 
"suitable" even mean in the context you're using it?



Or could you please tell me where to inquire?

There is this site named "https://www.google.com; which can be used to 
find a lot of useful information and some paid ads, too. As of lately, I 
prefer DDG, but that's just a personal preference.


If there were no takers for the first question, that may say something 
about the question. At this point, you gotta ask yourself one question 
only: do I feel lucky?


--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com




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

Re: Was my question inappropriate for postgres?

[Gavin Flower]

On 25/07/22 12:31, Mladen Gogala wrote:

On 7/24/22 20:15, Gavin Flower wrote:
I suspect that most people do not know what 'M365' is, would be good 
to explain.  Am curious as to what 'M365' is! 


M365 is the latest member in the ArmaLite M-16 family of products. It 
can be connected to database to track down the DBA who don't check 
their backups.



[...]

Re: Was my question inappropriate for postgres?

[Gavin Flower]

On 25/07/22 12:19, Adrian Klaver wrote:

On 7/24/22 17:15, Gavin Flower wrote:

On 25/07/22 11:56, Taka Taka wrote:

Hello.


I suspect that most people do not know what 'M365' is, would be good 
to explain.  Am curious as to what 'M365' is!


New branding and pricing(subscription) of MS Office.

Microsoft 365
https://www.microsoft.com/en-us/microsoft-365


[...]

Thanks,  I use LibreOffice which is free and easier to use -- it runs on 
many O/S's including those from Micosoft.

https://www.libreoffice.org/

Re: Was my question inappropriate for postgres?

[Mladen Gogala]

On 7/24/22 20:15, Gavin Flower wrote:
I suspect that most people do not know what 'M365' is, would be good 
to explain.  Am curious as to what 'M365' is! 


M365 is the latest member in the ArmaLite M-16 family of products. It 
can be connected to database to track down the DBA who don't check their 
backups.


--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

Re: Was my question inappropriate for postgres?

[Mladen Gogala]

On 7/24/22 19:56, Taka Taka wrote:

Hello.

I posted question bellow.



And? No takers? I wonder why. Maybe the question is unclear? This is 
really surprising :)




Hello.
I would like to know if psqlodbc_13_02 is compatible with M365.
Also, could you please tell me which of the psqlodbc_13_02 would be
suitable to Windows 10 64-bit?


What is M365? Is it a part of Microsoft Office 365, aka "O365"? Is it 
related to M-16? What di you mean by "driver suitable for Windows 10"? 
If the driver can be installed and configured by the MS ODBC driver 
administrator, then I guess it's suitable. Make sure the driver you use 
is mauve, they take the least RAM




Was my question inappropriate?
I wanted to know if the pdbc driver above would be compatible to 
Microsoft's M365.

What part of Office 365 do you want the compatibility with? Excel?

Also, I find variety in the odbc driver.


I don't. ODBC drivers implement the same protocol when communicating 
with the database and they all work the same. Here is what I have:


[mgogala@umajor ~]$ rpm -qa *odbc*
oracle-instantclient-odbc-21.5.0.0.0-1.x86_64
postgresql-odbc-13.01.-2.fc36.x86_64
msodbcsql17-17.10.1.1-1.x86_64
[mgogala@umajor ~]$

The string "f36" in the PostgreSQL ODBC package name denotes a very 
special version of Windows, more advanced than even Windows 11. I was 
able to connect all 3 of the drivers above to LibreOffice and use them 
to query the underlying database. I was able to do the same on my 
Windows 8.1, with all 3 of those drivers.


Is it possible to know which of the driver is suitable to Windows 10 
64-bit?


Suitable how? What exactly do you want to do? What does the word 
"suitable" even mean in the context you're using it?



Or could you please tell me where to inquire?

There is this site named "https://www.google.com; which can be used to 
find a lot of useful information and some paid ads, too. As of lately, I 
prefer DDG, but that's just a personal preference.


If there were no takers for the first question, that may say something 
about the question. At this point, you gotta ask yourself one question 
only: do I feel lucky?


--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

Re: Was my question inappropriate for postgres?

[Adrian Klaver]

On 7/24/22 17:15, Gavin Flower wrote:

On 25/07/22 11:56, Taka Taka wrote:

Hello.


I suspect that most people do not know what 'M365' is, would be good to 
explain.  Am curious as to what 'M365' is!


New branding and pricing(subscription) of MS Office.

Microsoft 365
https://www.microsoft.com/en-us/microsoft-365





Cheers,
Gavin






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

Re: Was my question inappropriate for postgres?

[Gavin Flower]

On 25/07/22 11:56, Taka Taka wrote:

Hello.

I posted question bellow.

Hello.
I would like to know if psqlodbc_13_02 is compatible with M365.
Also, could you please tell me which of the psqlodbc_13_02 would be
suitable to Windows 10 64-bit?
Was my question inappropriate?
I wanted to know if the pdbc driver above would be compatible to 
Microsoft's M365.

Also, I find variety in the odbc driver.
Is it possible to know which of the driver is suitable to Windows 10 
64-bit?

Or could you please tell me where to inquire?


Hi,

Please do not top post, bottom post like me.

I suspect that most people do not know what 'M365' is, would be good to 
explain.  Am curious as to what 'M365' is!


Can't help with Microsoft stuff, as I mainly use Linux, though I've been 
forced to use Microsoft O/S's in the past (last time was in a VM on 
Linux a few years back).


If you are using Postgres for really large data volumes, and/or require 
high performance, you might be better to upgrade to Linux --- it really 
depends on your own specific situation.  Note that most servers run Linux!



Cheers,
Gavin

Re: Was my question inappropriate for postgres?

[Adrian Klaver]

On 7/24/22 16:56, Taka Taka wrote:

Hello.

I posted question bellow.

Hello.
I would like to know if psqlodbc_13_02 is compatible with M365.
Also, could you please tell me which of the psqlodbc_13_02 would be
suitable to Windows 10 64-bit?

Was my question inappropriate?


It was appropriate. It is just that Windows users are not all that 
common on this list, especially that subset that are using ODBC.


You might have better luck subscribing to and asking question on the 
Postgres ODBC list:


https://www.postgresql.org/list/pgsql-odbc/

I wanted to know if the pdbc driver above would be compatible to 
Microsoft's M365.

Also, I find variety in the odbc driver.
Is it possible to know which of the driver is suitable to Windows 10 64-bit?


I would say from here:

https://www.postgresql.org/ftp/odbc/versions/msi/

psqlodbc_13_02_-x64.zip psqlodbc_13_02_-x64.zip

Try it and see, if you don't want to wait for response from this list or 
the ODBC list.




Or could you please tell me where to inquire?




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

Was my question inappropriate for postgres?

[Taka Taka]

Hello.

I posted question bellow.

Hello.
I would like to know if psqlodbc_13_02 is compatible with M365.
Also, could you please tell me which of the psqlodbc_13_02 would be
suitable to Windows 10 64-bit?

Was my question inappropriate?
I wanted to know if the pdbc driver above would be compatible to
Microsoft's M365.
Also, I find variety in the odbc driver.
Is it possible to know which of the driver is suitable to Windows 10 64-bit?
Or could you please tell me where to inquire?

Re: Queries in another user's tables

[Adrian Klaver]

On 7/24/22 00:37, xav...@datolibre.com wrote:
Hello friends, I have a database base01 that belongs to user01 and on 
the other hand a datebase base02 that belongs to user02. I need user01 
to make a query "SELECT * FROM base02.yourtable;"


How can you get this?



In what client are you doing this?


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

Re: Strange behavior between timestamp and date comparison

[Adrian Klaver]

On 7/23/22 03:04, Ludwig Isaac Lim wrote:

Hello:

Below is a sample case that exhibits a behavior that I can't explain:

-- create the table
create table ts (t timestamp without time zone);

-- populate
insert into ts(t) values ('2022-07-16 00:22:06.974000');
insert into ts(t) values ('2022-07-16 00:22:06.974000');
insert into ts(t) values ('2022-07-16 00:22:06.974000');



-- This one return expected results
select * from ts where t::date between '2022-07-16'::Date - make_interval(days 
=> 30) and '2022-07-16'::Date;
             t
-
  2022-07-16 00:22:06.974
  2022-07-16 00:22:06.974
  2022-07-16 00:22:06.974
(3 rows)


-- This one doesn't return anything (unexpected)
  select * from ts where t between '2022-07-16'::Date - make_interval(days => 
30) and '2022-07-16'::Date;
  t
---
(0 rows)


Because:

select '2022-07-16 00:22:06.974'::date;
date

 2022-07-16


select '2022-07-16 00:22:06.974'::timestamp;
timestamp
-
 2022-07-16 00:22:06.974


and:

 select '2022-07-16'::date::timestamp;
  timestamp
-
 2022-07-16 00:00:00


When you normalize all the values to a date it works e.g. t::date.

When you don't then the date values in the between get compared as 
timestamps and 2022-07-16 00:22:06.974 is greater then 2022-07-16 00:00:00




-- version
select version();
                                                  version
-
  PostgreSQL 14.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.3.1 20180712 
(Red Hat 7.3.1-15), 64-bit
(1 row)



Regards,
Ludwig Lim






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

Re: Strange behavior between timestamp and date comparison

[Rory Campbell-Lange]

On 23/07/22, Ludwig Isaac Lim (ludz_...@yahoo.com) wrote:
> Below is a sample case that exhibits a behavior that I can't explain:
> 
> -- create the table
> create table ts (t timestamp without time zone);
> 
> -- populate
> insert into ts(t) values ('2022-07-16 00:22:06.974000');
> insert into ts(t) values ('2022-07-16 00:22:06.974000');
> insert into ts(t) values ('2022-07-16 00:22:06.974000');

> -- This one doesn't return anything (unexpected)
>  select * from ts where t between '2022-07-16'::Date - make_interval(days => 
> 30) and '2022-07-16'::Date;

It looks like all of your timestamps are outside of the upper bound of 
"between".

template1=> select ('2022-07-16'::Date)::timestamp;
  timestamp
-
 2022-07-16 00:00:00

Strange behavior between timestamp and date comparison

[Ludwig Isaac Lim]

Hello:

Below is a sample case that exhibits a behavior that I can't explain:

-- create the table
create table ts (t timestamp without time zone);

-- populate
insert into ts(t) values ('2022-07-16 00:22:06.974000');
insert into ts(t) values ('2022-07-16 00:22:06.974000');
insert into ts(t) values ('2022-07-16 00:22:06.974000');



-- This one return expected results
select * from ts where t::date between '2022-07-16'::Date - make_interval(days 
=> 30) and '2022-07-16'::Date;
            t
-
 2022-07-16 00:22:06.974
 2022-07-16 00:22:06.974
 2022-07-16 00:22:06.974
(3 rows)


-- This one doesn't return anything (unexpected)
 select * from ts where t between '2022-07-16'::Date - make_interval(days => 
30) and '2022-07-16'::Date;
 t
---
(0 rows)

-- version
select version();
                                                 version
-
 PostgreSQL 14.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.3.1 20180712 
(Red Hat 7.3.1-15), 64-bit
(1 row)



Regards,
Ludwig Lim

Unable to archive logs in standby server

[Meera Nair]

Hi team,

With non-exclusive backup method, trying backup from standby node.
But pg_stop_backup function returns "WAL archiving is not enabled..." and the 
logs are not archived to WAL directory configured.

Please check if I am missing anything in configuring this properly,
Server was restarted after setting the archiving params in postgresql.conf

Below is from version 14:

postgres=# select pg_start_backup('label', false, false);
pg_start_backup
-
0/6D8
(1 row)


postgres=#  select pg_stop_backup('false');
NOTICE:  WAL archiving is not enabled; you must ensure that all required WAL 
segments are copied through other means to complete the backup
  pg_stop_backup
---
(0/60001C0,"START WAL LOCATION: 0/6D8 (file 00010006)+
CHECKPOINT LOCATION: 0/6000110   +
BACKUP METHOD: streamed  +
BACKUP FROM: standby +
START TIME: 2022-07-21 12:42:11 IST  +
LABEL: label +
START TIMELINE: 1+
","")
(1 row)


postgres=# select pg_is_in_recovery();
pg_is_in_recovery
---
t
(1 row)


postgres=# show wal_level;
wal_level
---
replica
(1 row)


postgres=# show archive_mode;
archive_mode
--
on
(1 row)


postgres=# show archive_command;
archive_command

copy "%p" "D:\PostgreSQL\14\standby_14\wal\%f"
(1 row)



Regards,
Meera

Re: Queries in another user's tables

[Pavel Stehule]

Hi


ne 24. 7. 2022 v 9:38 odesílatel  napsal:

> Hello friends, I have a database base01 that belongs to user01 and on the
> other hand a datebase base02 that belongs to user02. I need user01 to make
> a query "SELECT * FROM base02.yourtable;"
>
> How can you get this?
>

see https://www.postgresql.org/docs/current/postgres-fdw.html

Regards

Pavel

Re: Logging the query executed on the server

[Steve Baldwin]

On Sun, Jul 24, 2022 at 4:29 PM Igor Korot  wrote:

>
> 2 things:
> 1. How do I turn this off? ;-)
>

When you change the setting via 'set', that change is only for the current
session. You can revert it with 'set {some param} to default;' or just
terminate the session. If you want to make the change permanent, you need
to set it in the config file.

2. The log does show the query but it shows it with the placeholders.
> Is there a way to see the actual query?
>

Not sure what you mean here. The query shown in the log should be what is
actually executed by the server. If you are using placeholders, there are
probably 3 different log entries - one for the parse step, one for the bind
step and one for the execute step. If you are asking what are the bind
variable values, they are shown in the bind step. For example:

2022-07-24 07:00:00
UTC:10.122.33.196(33732):b2bc_api@b2bcreditonline:[7786]:LOG:
duration: 0.072 ms bind :
select public.currency_on_mismatch() as on_mismatch,
set_config('search_path', $1, true),
set_config('application.user_id', $2, true),
set_config('application.app_client', $3, true),
set_config('application.api_client_id', $4 , true),
set_config('application.source', $5 , true),
set_config('application.request_id', $6 , true),
set_config('application.in_test_context', $7, true),
set_config('lock_timeout', $8, true),
txid_current()
2022-07-24 07:00:00
UTC:10.122.33.196(33732):b2bc_api@b2bcreditonline:[7786]:DETAIL:
parameters: $1 = 'public', $2 = 'Admin/Support', $3 = 'Admin/Support', $4 =
'ce34e2bc-2c65-4fc1-9b95-878aef19a348', $5 = '?', $6 =
'4432dbb2-ab1c-4bd8-a413-ff5c704209a6', $7 = 'f', $8 = '10s'

If this doesn't help, maybe post what you're seeing in the log.

Cheers,

Steve

Re: Logging the query executed on the server

[Igor Korot]

Hi, Steve,

On Sun, Jul 24, 2022 at 12:51 AM Steve Baldwin  wrote:
>
> Hi Igor,
>
> Before you issue your query, try something like this:
>
> (from psql, but hopefully you get the idea)
>
> b2bcreditonline=# set log_min_duration_statement to 0;
> SET
> b2bcreditonline=# set log_statement to 'all';
> SET
>
> Ref: https://www.postgresql.org/docs/current/sql-set.html, 
> https://www.postgresql.org/docs/current/config-setting.html
>
> Then submit your query and it will be set to the server log. You can get the 
> name of the current logfile with:
>
> b2bcreditonline=# select pg_current_logfile();
>  pg_current_logfile
> 
>  /log/pg.csv

2 things:
1. How do I turn this off? ;-)
2. The log does show the query but it shows it with the placeholders.
Is there a way to see the actual query?

Thank you.


>
> HTH,
>
> Steve
>
> On Sun, Jul 24, 2022 at 3:26 PM Igor Korot  wrote:
>>
>> Hi,
>> Is it possible to log the query that will be executed
>> on the server?
>>
>> I'm writing an application that connects to the server
>> through ODBC and libpq.
>> For some reason ODBC interface is failing - it desn't
>> return any rows
>>
>> So I'm thinking if I have a proof that the query I am
>> actually executing is the same as the one I run through
>> the psql - I will know where to look.
>>
>> I am actually binding some parameters and trying to
>> execute the query.
>>
>> Thank you.
>>
>>