Re: [GENERAL] Create Action for psql when NOTIFY Recieved

[Nico Williams]

On Mon, Sep 4, 2017 at 4:21 PM Steve Atkins  wrote:

> >
>
> Me too.
>
> https://github.com/wttw/pgsidekick
>
> Select-based, sends periodic keep-alives to keep the connection open,
> outputs payloads in a way that's friendly to pipe into xargs. (Also the
> bare bones of a notify-based scheduler).


Without any kind of access controls on NOTIFY channels, nor any kind of
payload validation, i just don't feel comfortable using the payload at
all.  Besides, the payload is hardly necessary given that there's a
database on which you can scribble the payload :)  It suffices that you
receive a notification, and you can then check if there's anything to do.

My version of this doesn't have connection keepalives, but that's ok
because that can be added in the form of notifications, and the
consumer of pqasyncnotifier can implement timeouts.  But i agree that
timeouts and keepalives would be nice, and even invoking a given SQL
function would be nice.

But the question i have is: how to get such functionality integrated into
PostgreSQL?  Is a standalone program (plus manpage plus Makefile changes)
enough, or would a psql \wait command be better?

Nico
-- 

>
>

Re: [GENERAL] What's the best way in postgres to use ANY() with LIKE '%'?

[Ryan Murphy]

> I'm pretty sure it doesn't work syntactically.  Don't recall the details
offhand.

Ok, thanks!

Re: [GENERAL] What's the best way in postgres to use ANY() with LIKE '%'?

[Tom Lane]

Ryan Murphy  writes:
> Interesting! It seems like one "simple" possiblity would be to allow ANY()
> to be on either side...or would that muck up the Grammar too badly or have
> weird edge cases where it doesn't make sense?

I'm pretty sure it doesn't work syntactically.  Don't recall the details
offhand.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] What's the best way in postgres to use ANY() with LIKE '%'?

[Ryan Murphy]

>
> I'm not sure why we've never got round to providing such a thing
> in core ... probably lack of consensus on what to name the reverse
> operator.  You'd need to support regex cases as well, so there's
> more than one operator name to come up with.
>

Interesting! It seems like one "simple" possiblity would be to allow ANY()
to be on either side...or would that muck up the Grammar too badly or have
weird edge cases where it doesn't make sense?


>
> regards, tom lane
>

Re: [GENERAL] What's the best way in postgres to use ANY() with LIKE '%'?

[Tom Lane]

Ryan Murphy  writes:
> But is there any way to do:
> select * from post
> where any(tags) LIKE 'music%';
> ??
> This doesn't work because ANY is only allowed on the right.

Yeah.  The traditional answer is "make yourself a reverse LIKE
operator, one that takes the pattern on the left".

You can brute-force this in a couple of lines with a SQL function
and a CREATE OPERATOR command.  But it won't be tremendously
efficient like that.  If performance is critical it'd be worth
writing the shim function in C.

I'm not sure why we've never got round to providing such a thing
in core ... probably lack of consensus on what to name the reverse
operator.  You'd need to support regex cases as well, so there's
more than one operator name to come up with.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] hidden maintenance_work_mem limitations of a Windows build

[reugen1984]

Hi PG developers, it has recently come to my attention that Win and Nix flavors 
or Postgres (including upcoming 10.0 beta 3) are not equal in terms of limits 
on maintenance_work_mem parameter. Even when you try to set it to miserable 
2gb, on Windows you get  "outside the valid range for parameter 
"maintenance_work_mem" (1024 .. 2097151)". But i never knew before that Linux 
users can set this parameter to any amounts they like, without such artificial 
restriction. 
I used to wait for index creation for WEEKS on Windows using only 2Gb for 
maintenance_work_mem while having a server with 128Gb RAM. Especially "nice" 
feelings I have experienced when the server had a power outage, and 8 day's 
work of index creation (of course, single-threaded, while again, server had 32 
cores) was lost.

Section  
https://www.postgresql.org/docs/current/static/runtime-config-resource.html  of 
the PG docs does not say a word that Windows is not a first-class citizen when 
dealing with  maintenance_work_mem, which would save me and presumably many 
other developers tons of time and nerves.

Here are my questions:
1) Is this really impossible to permit usage of more than 2Gb of  
maintenance_work_mem in Windows version?
2) If still not, could you please update docs to reflect this limitation of a 
Windows build so that people don't waste their time with Windows version and go 
for Linux one right away if they 
have large tables (starting form ~100Gb) and want to index them for faster 
searches? Necessity to wait for indexing to complete for weeks while having 32 
cores 128Gb RAM SSD-featured rig, given that RDBMS is not really using 
available resources, really plays against PG reputation.
3) Any plans to make indexing operation multi-threaded? I hoped to see this 
feature in the upcoming release, without it it's not 10.0 but 9.7 in my humble 
opinion :-)
4) Any plans to make indexing operation resumable after an unexpected 
postmaster shutdown (ie periodical saving of current indexing operation 
progress)?
5) Any plans to allow inclusion of index data into backup & restore ecosystem?

Regards, 
Eugen

[GENERAL] What's the best way in postgres to use ANY() with LIKE '%'?

[Ryan Murphy]

e.g. I know you can do

select * from post
where 'music' = any(tags);

Which is similar to saying tags @> '{music}'.

And I see that I can even do:

select * from post
where 'music' LIKE any(tags);

...implying that ANY is more general in some ways than @>,
e.g. it can would with LIKE as well as =.

But is there any way to do:

select * from post
where any(tags) LIKE 'music%';

??

This doesn't work because ANY is only allowed on the right.

Thanks!
Ryan

Re: [GENERAL] Create Action for psql when NOTIFY Recieved

[Steve Atkins]

> On Sep 3, 2017, at 3:32 PM, Nico Williams  wrote:
> 
> 
> My principal problem with psql(1) relative to NOTIFY/LISTEN is that
> psql(1) won't check for them until it has had some input on stdin.  So
> it will appear to do nothing when it's idle, even if there millions of
> notifies for it to respond to!
> 
> So I wrote a program to just LISTEN: 
> https://github.com/twosigma/postgresql-contrib/blob/master/pqasyncnotifier.c

Me too.

https://github.com/wttw/pgsidekick

Select-based, sends periodic keep-alives to keep the connection open, outputs 
payloads in a way that's friendly to pipe into xargs. (Also the bare bones of a 
notify-based scheduler).

Cheers,
  Steve



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Surprising locking behavior with CTE, FOR SHARE, and UPDATE

[Seamus Abshere]

I have a query that splits up work (and manually does locking) according
to an id range:

WITH
new_data AS (
  SELECT [...] FROM data
  WHERE id BETWEEN 1 AND 2 -- here's my "id range"
),
old_data AS (
  SELECT [...] FROM data
  WHERE id IN (SELECT id FROM new_data)
  FOR UPDATE -- a manual lock to prevent race conditions
)
UPDATE data
SET [...]
FROM
new_data,
old_data
WHERE [...]

But I see that queries are blocking each other from non-overlapping id
ranges. For example, "BETWEEN 1 AND 2" is blocking "BETWEEN 5 AND 6".
This is Postgres 9.6.3.

Would it help to "redundantly" add the id ranges everywhere? (e.g, in
the where clauses of old_data AND the final update)?

Thanks!

--
Seamus Abshere, SCEA
https://www.faraday.io
https://github.com/seamusabshere
https://linkedin.com/in/seamusabshere


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Undefined Reference

[Stefan Wagner]

I'm apologizing in advance for the double post. Since my first one wasn't composed very thoughtfull and hasn't gotten any responses.

 

Im new to postgresql and trying to create a new Clause in the source code. Thanks to some advice in this Mailing List I've been able to start coding.

 

I'm using Windows 7 as a operating System and building the source code with MSYS2 MingGw.

 

It seems I'm missing something very simple in the Libary Files looking at the Error Code.

But since im new to this it really feels like a Roadblock can someone give me advice what to do or where to add the missing references?

 

I added a File src\backend\executor\nodeDelFil which includes #include "utils/tuplewindow.h" but I get following Error:

 


x86_64-w64-mingw32-gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement

-Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard

-O2 -L../../src/port -L../../src/common -Wl,--allow-multiple-definition -Wl,--disable-auto-import  -Wl,--as-needed

  -Wl,--stack=4194304 -Wl,--export-all-symbols -Wl,--out-implib=libpostgres.a access/brin/brin.o access/brin/brin_pageops.o...

... ../../src/common/libpgcommon_srv.a win32ver.o -lws2_32 -lm -lws2_32 -lsecur32 -o postgres.exe
make[2]: *** [Makefile:86: postgres] Error 1..

..executor/nodeDelFil.o:nodeDelFil.c:(.text+0x22): undefined reference to `tuplewindow_end'
executor/nodeDelFil.o:nodeDelFil.c:(.text+0x43): undefined reference to `tuplewindow_begin'
executor/nodeDelFil.o:nodeDelFil.c:(.text+0x306): undefined reference to `tuplewindow_rewind'
executor/nodeDelFil.o:nodeDelFil.c:(.text+0x324): undefined reference to `tuplewindow_ateof'
executor/nodeDelFil.o:nodeDelFil.c:(.text+0x336): undefined reference to `tuplewindow_gettupleslot'
executor/nodeDelFil.o:nodeDelFil.c:(.text+0x365): undefined reference to `tuplewindow_movenext'
executor/nodeDelFil.o:nodeDelFil.c:(.text+0x36d): undefined reference to `tuplewindow_ateof'
executor/nodeDelFil.o:nodeDelFil.c:(.text+0x385): undefined reference to `tuplewindow_puttupleslot'
executor/nodeDelFil.o:nodeDelFil.c:(.text+0x3a9): undefined reference to `tuplewindow_removecurrent'
executor/nodeDelFil.o:nodeDelFil.c:(.text+0x3da): undefined reference to `tuplewindow_setinsertrank'
executor/nodeDelFil.o:nodeDelFil.c:(.text+0x413): undefined reference to `tuplewindow_setinsertrank'
collect2.exe: error: ld returned 1 exit status

 

Every Help would be greatly appreciated.