SV: compiling postgres on windows - how to deal with unmatched file extension?

>I'm trying to build postgresql from source code on windows. The build ran into >a bunch of errors like > >'src/backend/bootstrap/bootparse.c': No such file or directory >src/backend/parser/gram.c': No such file or directory > >These .c files are referenced in postgres.vcxproj. They actual exist b

SV: GSSAPI authentication

Thank you for your suggestion. I arrived at the same suspicion. And that was it. Reverse DNS was not set up correctly. Fra: Michael van der Kolff Sendt: 6. juni 2022 15:50 Til: Niels Jespersen Cc: pgsql-general list Emne: Re: GSSAPI authentication From the tiny bit I know about this, and a

SV: GSSAPI authentication

Fra: Michael van der Kolff Sendt: 6. juni 2022 14:26 Til: Niels Jespersen Cc: pgsql-general list Emne: Re: GSSAPI authentication >This sounds like your PG service was unable to authenticate itself to AD. > >There's probably a trick to that somewhere - AD doesn't really want

GSSAPI authentication

xxlocal include_realm=0 krb_realm=""XXX.LOCAL""""""","client backend",,-3382135431624836920 We are a bit lost here. What are we missing? Regards Niels Jespersen

Logon via GSSAPI from Linux fails, but works from Windows

"authentication",2022-05-13 18:14:01 CEST,2/14544,0,FATAL,28000,"GSSAPI authentication failed for user ""yyy""","Connection matched pg_hba.conf line 15: ""hostall all 172.0.0.0/8 gss map=xxxlocal include_realm=0 krb_realm=""XXX.LOCAL""""""","client backend",,-3382135431624836920 Are we forgetting to set something up? Regards Niels Jespersen

Folding of case of identifiers

f these three create tables, the two first succeed, the last one does not (G and g is equivalent, Æ and æ is not). create table æblegrød (a int, køn text); create table ÆblegrØd (a int, køn text); create table ÆbleGrØd (a int, køn text); Can anyone explain the logic that rules this. Regards Niels Jespersen

SV: Possibilities for optimizing inserts across oracle_fdw foreign data wrapper

-Oprindelig meddelelse- >Fra: Laurenz Albe >Sendt: 20. september 2021 15:18 >Til: Niels Jespersen ; pgsql-gene...@postgresql.org >Emne: Re: Possibilities for optimizing inserts across oracle_fdw foreign data >wrapper > >On Sun, 2021-09-19 at 10:28 +, Niels Jespe

Possibilities for optimizing inserts across oracle_fdw foreign data wrapper

? If I could make the Oracle insert direct load, that would usually also increase throughput. But, is that possible here. There are no constraints defined on the destinaton tables. Regards Niels Jespersen

SV: cpu-intensive immutable function and parallel scan

>Fra: David Rowley Sendt: 22. juni 2021 09:10 >Emne: Re: cpu-intensive immutable function and parallel scan >On Tue, 22 Jun 2021 at 19:06, Niels Jespersen wrote: >> I think I could achieve some speedup by parallelizing further, distributing >> the cpu-work among additional

cpu-intensive immutable function and parallel scan

Hello all I have a cpu-intensive plpython3u function that computes a result on the value from a single column value from a simple select.This looks largely like this. select function_name (t1.val1, 'constant1') from t1 where t1.p = '202012_1' and t1.val1 is not null; The function is marked imm

SV: force partition pruning

Fra: David Rowley Sendt: 12. maj 2021 02:34 >> >> ok i think i just may be there is very less data , hence no index scan, no >> pruning. >> >> when i try to force seq_scan off, >> > >Unfortunately, no run-time pruning occurred in the above plan. > >The fact that the above plan uses Append made

SV: force partition pruning

> >Sorry, > >I made a major mistake. I somehow saw the period and period_version as the >same. >so, yes partitions are not pruned here. So my suggestion makes no sense. Thats quite ok. I think my plan now is to have a table returning function that executes a query dynamically. The query has a wh

force partition pruning

es and each datatable has a different set of columns. This excludes a table returning function since that must list the columns present. Best regards Niels Jespersen Chief Adviser IT Center Mobile phone:+45 42 42 93 73 Email: n...@dst.dk Statistics Denmark, Sejrøgade 11, DK-2100 Copenhagen ww

SV: where clauses including timstamptz and intervals

>-Oprindelig meddelelse- >Fra: Francisco Olarte >Sendt: 10. april 2021 09:31 >Til: Niels Jespersen > >Although it is equivalent I would suggest to use: > >f.r_time< ('2020-10-01 00:00:00+00'::timestamptz at time zone 'utc' + interval >&

SV: where clauses including timstamptz and intervals

>-Oprindelig meddelelse- >Fra: Tom Lane >Emne: Re: where clauses including timstamptz and intervals > >Ron writes: >> On 4/9/21 5:24 AM, hubert depesz lubaczewski wrote: >>> For example, in my timezone: >>> >>> (1 row) >>> >>> Please note that there is 1 hour difference. >>> The reason

where clauses including timstamptz and intervals

::timestamptz and f.r_time < ('2020-10-01 00:00:00+00'::timestamptz + interval '1 month'); select f.xx from f where f.r_time >= '2020-10-01 00:00:00+00'::timestamptz and f.r_time < ('2020-11-01 00:00:00+00'::timestamptz); Regards Niels Jespersen

SV: design partioning scheme for selecting from latest partition

>Fra: Francisco Olarte >Sendt: 22. marts 2021 20:04 >Til: Niels Jespersen >Cc: pgsql-general@lists.postgresql.org >Emne: Re: design partioning scheme for selecting from latest partition > >Niels: > >On Mon, Mar 22, 2021 at 3:40 PM Niels Jespersen wrote: >... &

design partioning scheme for selecting from latest partition

there another way to do this in a cheaper way. For now I have created a materialized view based on the select above, thus only scanning for max partition only once. Niels Jespersen

SV: Log files polluted with permission denied error messages after every 10 seconds

> > >Fra: Andrus >Sendt: 8. marts 2021 23:24 >Emne: Re: Log files polluted with permission denied error messages after every >10 seconds > >Permission denied error occur in Windows. Debian logs doesnt contain it. >In Windows also pg_database_size('mydb') and similar ones fail frequently >with

SV: Insertion time is very high for inserting data in postgres

to-and-copy-from This is THE way of high-performant inserts using Postgres. Regards Niels Jespersen

SV: table returning function for each row in other resultset

Fra: David G. Johnston Sendt: 3. februar 2021 16:08 >On Wed, Feb 3, 2021 at 8:01 AM Niels Jespersen wrote: >Hello all > >I have som data in a resultset. E.g: > >id date_begin date_end amount >1 2021-01-04 2021-02-06 100 >2 2021-03-17 2021-05-11 234 > >I h

SV: SV: Npgsql and the Connection Service File

anage this? >> >>Either they don't or they write their own class that does that. >> >>Perhaps you should contact Npgsql and suggest such a feature. >> >Yes, thank you. >I did just that. It turns out there is already an existing pull request with >exact

table returning function for each row in other resultset

-17,2021-03-31,63.818182 2,2021-04-01,2021-04-30,127.636364 2,2021-05-01,2021-05-11,46.8 How can I accomplish this, please. Regards Niels Jespersen

SV: SV: Npgsql and the Connection Service File

>On Sat, 2021-01-30 at 15:56 +0000, Niels Jespersen wrote: >> It would be nice if Npgsql (and jdbc and others) emulated the libpq >> behaviour. >> Because in my mind, abstracting hostname, portnumber and databasename >> away is a really useful feature. >&

SV: Npgsql and the Connection Service File

> > >-Oprindelig meddelelse- >Fra: Laurenz Albe >Sendt: 29. januar 2021 20:14 >Til: Niels Jespersen ; pgsql-general@lists.postgresql.org >Emne: Re: Npgsql and the Connection Service File > >On Fri, 2021-01-29 at 14:13 +, Niels Jespersen wrote: >

Npgsql and the Connection Service File

Hello all Is there a way to get Npgsql understand the Connection Service File (https://www.postgresql.org/docs/13/libpq-pgservice.html). ? The purpose is to abstract physical details such as hostname, portnumber away from the application. Regards Niels Jespersen

Re: time-based range partitioning and truncate/delete different timezones

Fra: Michael Lewis Dato: 15. januar 2021 kl. 19.49.32 CET Til: Niels Jespersen Cc: pgsql-gene...@postgresql.org Emne: Re: time-based range partitioning and truncate/delete different timezones What version are you using? How long are you keeping data for

time-based range partitioning and truncate/delete different timezones

ution? Delete from t where t.timecolumn between a and b, and reinsert the new data. My instinct says no, but I cannot really think of a good alternative. It is 100 million rows with 5 or 6 numeric columns. Regards Niels Jespersen

SV: Tools showing table partitions as tables in listings of tables

>> A small irritation point is that some tools decide that partitions >> under a table are to be shown in a list of tables, sometimes drowning the >> main table in a sea of partitions. > >While this doesn't answer your question directly, but when I had this problem, >I simply moved partitions to

Tools showing table partitions as tables in listings of tables

and these queries are naive in respect to table partitions. Any ideas for working around this? Naming partitions in a special manner, something else. It's not the end of the world, but quite irritating for some users. Regards Niels Jespersen

SV: PostgreSQL on Windows' state

-Oprindelig meddelelse- >Fra: Alessandro Dentella >Sendt: 23. september 2020 10:38 >Til: pgsql-general >Emne: PostgreSQL on Windows' state > >Hi, > >disclaimer: I've never been a Windows user and I send this email just on >behalf of a friend that has problems convincing his team to use

SV: Foreign tables, user mappings and privilege setup

>>On Mon, 2020-09-21 at 08:21 +, Niels Jespersen wrote: >> create user mapping for current_user server s... (user 'remoteuser', >> password 'remotepassword'); >> >> create foreign table t ( a int) server s... options (table_name >&

Foreign tables, user mappings and privilege setup

of the foreign table defined as 'security definer'. That's basically it. Shoot. Regards Niels Jespersen

SV: Kerberos-Postgresql implementation for user authentication

Fra: Brajendra Pratap Singh Sendt: 9. juli 2020 09:05 Til: pgsql-gene...@postgresql.org Emne: Kerberos-Postgresql implementation for user authentication Hi, Please share the steps to implement the Kerberos with postgresql for user authentication purpose. Also share the document if any. Thank

SV: SV: Using Postgres jdbc driver with Oracle SQL Developer

-Oprindelig meddelelse- >Fra: legrand legrand >Sendt: 6. juli 2020 17:36 >Til: pgsql-gene...@postgresql.org >Emne: Re: SV: Using Postgres jdbc driver with Oracle SQL Developer > >Try Replacing hostname by hostname/Database? >Don’t Forget ? > >Regards >Pascal Ha! Thats it!. Almost. Its

SV: Using Postgres jdbc driver with Oracle SQL Developer

Sorry, "way to provide a default username" should have been "way to provide a default databasename" Fra: Niels Jespersen Sendt: 6. juli 2020 13:20 Til: pgsql-general@lists.postgresql.org Emne: Using Postgres jdbc driver with Oracle SQL Developer Hello all Oracle SQL Dev

Using Postgres jdbc driver with Oracle SQL Developer

meters is says "If a property is specified both in URL and in Properties object, the value from Properties object is ignored." How can I inject a property object into the gap between Oracle and Postgres jdbc? Regards Niels Jespersen

SV: SV: pg_service.conf and client support

Fra: Laurenz Albe Sendt: 15. juni 2020 15:36 Til: Niels Jespersen ; pgsql-gene...@postgresql.org Emne: Re: SV: pg_service.conf and client support On Mon, 2020-06-15 at 11:58 +, Niels Jespersen wrote: > > For your examples that means: > > - The PostgreSQL ODBC server can use pg_

SV: pg_service.conf and client support

-Oprindelig meddelelse- Fra: Laurenz Albe Sendt: 15. juni 2020 11:24 Til: Niels Jespersen ; pgsql-gene...@postgresql.org Emne: Re: pg_service.conf and client support On Sun, 2020-06-14 at 05:03 +, Niels Jespersen wrote: > I just found out about the pg service file. >

pg_service.conf and client support

er support, that can be used instead of pg_service.conf (if support is scarce beyond what builds on libpq)? Thank you. Regards Niels Jespersen

SV: what to log in csvlogs

fic tables? Thanks, Mary The Pgaudit extension will let you audit specific actions to specific tables. Regard Niels Jespersen

SV: timestamp and timestamptz

Fra: Magnus Hagander Sendt: 16. april 2020 10:28 Til: Niels Jespersen Cc: pgsql-general@lists.postgresql.org Emne: Re: timestamp and timestamptz On Thu, Apr 16, 2020 at 6:12 AM Niels Jespersen mailto:n...@dst.dk>> wrote: Fra: Magnus Hagander mailto:mag...@hagander.net>> Send

SV: timestamp and timestamptz

Fra: Magnus Hagander Sendt: 15. april 2020 20:05 Til: Niels Jespersen Cc: pgsql-general@lists.postgresql.org Emne: Re: timestamp and timestamptz On Wed, Apr 15, 2020 at 7:50 PM Niels Jespersen mailto:n...@dst.dk>> wrote: Hello all We have some data that have entered a timestamp

timestamp and timestamptz

time zone 'utc')::timestamp read_time_cet, (read_time at time zone 'utc')::timestamptz read_time_tz from t limit 10; We are on Postgres 12. Regards Niels Jespersen

SV: Writing Postgres Extensions in C on Windows

Nothing special with the code for the extension itself. Same as other platforms. Building is another matter. I have written about compiling pgaudit on Windows on recent Postgres releases here. This could perhaps supplement the below blog. https://github.com/njesp/build_pgaudit_on_windows Reg

Re: SSPI auth and mixed case usernames

: 30. august 2019 kl. 16.21.39 CEST Til: Niels Jespersen Cc: Magnus Hagander , pgsql-general@lists.postgresql.org Emne: Re: SSPI auth and mixed case usernames Greetings, * Niels Jespersen (n...@dst.dk) wrote: >    Hello Magnus >    Thank you for your prompt reply.  >    I’m n

Re: SSPI auth and mixed case usernames

entered as XYz, the next day entered as xYz, should logon to Postgres user xyz. Niels Fra: Magnus Hagander Dato: 30. august 2019 kl. 13.31.33 CEST Til: Niels Jespersen Cc: pgsql-general@lists.postgresql.org Emne: Re: SSPI auth and mixed case usernames On Fri, Aug 30, 2019 at 1

SSPI auth and mixed case usernames

if so, how? Regards Niels Jespersen

SV: Oracle to postgres migration via ora2pg (blob data)

I would look at the source table in Oracle first. It looks a lot like audit data. Perhaps all content is not needed in Postgres. If it is, then the table and lobs may benefit from being reorganised in oracle. Alter table CLIENT_DB_AUDIT_LOG move; Alter table CLIENT_DB_AUDIT_LOG move lob (SYS_LO

RE: Implementing pgaudit extension on Microsoft Windows

allation. All is good. Perhaps my experiences could be added to the project. I am willing to write a short markdown document describing this, if there is interest. Regards Niels -Oprindelig meddelelse- Fra: Joe Conway Sendt: 14. februar 2019 00:34 Til: Niels Jespersen ; 'T

SV: SV: SV: Implementing pgaudit extension on Microsoft Windows

Will do. -Oprindelig meddelelse- Fra: Joe Conway Sendt: 14. februar 2019 00:34 Til: Niels Jespersen ; 'Tom Lane' Cc: 'Pavel Stehule' ; pgsql-general@lists.postgresql.org Emne: Re: SV: SV: Implementing pgaudit extension on Microsoft Windows On 2/13/19 9:17 AM, Jo

SV: SV: SV: Implementing pgaudit extension on Microsoft Windows

ra: Joe Conway Sendt: 13. februar 2019 15:18 Til: Niels Jespersen ; 'Tom Lane' Cc: 'Pavel Stehule' ; pgsql-general@lists.postgresql.org Emne: Re: SV: SV: Implementing pgaudit extension on Microsoft Windows On 2/13/19 3:32 AM, Niels Jespersen wrote: > Dive into the so

SV: SV: Implementing pgaudit extension on Microsoft Windows

15:14 Til: Niels Jespersen Cc: 'Pavel Stehule' ; pgsql-general@lists.postgresql.org Emne: Re: SV: Implementing pgaudit extension on Microsoft Windows Niels Jespersen writes: > Same result from > pgaudit.log = 'all' > in postgresql.conf and after both select pg_rel

SV: Implementing pgaudit extension on Microsoft Windows

Same result from pgaudit.log = 'all' in postgresql.conf and after both select pg_reload_conf(); and after service restart. No entries in the log from audit. Regards Niels Fra: Pavel Stehule Sendt: 12. februar 2019 09:01 Til: Niels Jespersen Cc: Tom Lane ; pgs

SV: Implementing pgaudit extension on Microsoft Windows

g meddelelse----- Fra: Tom Lane Sendt: 11. februar 2019 15:44 Til: Niels Jespersen Cc: 'pgsql-general@lists.postgresql.org' Emne: Re: Implementing pgaudit extension on Microsoft Windows Niels Jespersen writes: > Copied pgaudit.dll to C:\Program Files\PostgreSQL\11\lib Copied &g

Implementing pgaudit extension on Microsoft Windows

cked pgaudit.dll, so postgreSQL knows it is there, but nothing in the log-file. I need some advice regarding the next steps in my troubleshooting. Regards Niels Jespersen