not that trivial.
SQL is a base tool, if you want more expresiveness in your code you
should probably just use any of the mapper technologies there are
around. It is not that hard to make an SQL++ filter which translates
this kinds of things to SQL.
Francisco Olarte.
Abraham:
On Thu, 10 Apr 2025 at 13:30, Francisco Olarte wrote:
> You forgot to answer the first and most important question.
> > How are you connecting? Your symptoms matches confusing local with
> > TCP/IP to localhost. They are not the same thing.
After reading more messages i
re able to
connect via tcpip.
Francisco Olarte.
ocket/path will go to the local one, and no -h defaults to socket.
On a windows host it default to tcp/ip to localhost.
Francisco Olarte.
pt ).
I have done this with perl for some projects, built a driver which
defined several helper functions, then dofile("xx.dat") which returned
a big hash and then a series of loops on the result to write the SQL
in whatever order was neccessary.
Francisco Olarte.
in a debugging. You could do the same depending on
what domain your random ids are on.
Francisco Olarte.
t to text injectively ( but
not bijectively ) ( I mean, same values, same text, but you can have
different text same values ( like the obvious text->float conversion
))
Francisco Olarte.
I will also try to substitute the -c with a pipe. If it heals, it is
probably a quoting issue.
Also, I just caught Ron's message, and psql might be waiting for a password.
Francisco Olarte.
he max-paralell-worker stuff already
commented by Ron in an scenario with a long live locking processes (
vacuum full ) combined with potentially aggresive connecting ( a
benchmark tool ) I would verify the benchmark tool is not timing out
and disconnecting improperly leaving connections hung up.
Francisco Olarte.
number)::text) AND
(semester = a.semester))"
Which seems much more selective and recovers just what it wants.
I would start by analyzing ( and, if not too costly, reindexing ) that table.
Francisco Olarte.
ation. If they are, then it is time to see why the old one
does it slower ( I assume you are not testing a busy production server
against an idling backup). If they do not, then the path to follow is
to compare plans and try to know why they differ.
> Regards.
> Francisco Olarte.
As an aside, I would personally appreciate it if you delete my
signature from the end of your message when replying to one sent by
me.
Francisco Olarte.
olution would be "use fast
server". As I said, maybe having more data we could suggest "analyze
that table with these parameters", or "make this index" or "rewrite
this condition in this way", but this is impossible to do with the
data you provided.
Regards.
Francisco Olarte.
be working for years before you hit the bad
spot. Also, the query includes todays date, so I doubt it has been
used for years, probably "a similar one has been used for years", and
probably that is not your real table ( or you have a naming problem ).
Without giving real info, people cannot give you real solutions.
Francisco Olarte.
; with a(x) as ( SELECT '001.00MONTHS'::interval) select x,
pg_typeof(x) from a;
x | pg_typeof
---+---
1 mon | interval
(1 row)
( I used fractions, uppercase and no spaces on input to show how
interval output normalizes ).
Francisco Olarte.
it pushes the
future problem further down, nearly forever for comms between
different machines until someone develops FTL networks.
Francisco Olarte.
lacking from POSIX, for control
purposes, but only a little.
You could try "df -h /dev/shm" and "ls -lhR /dev/shm/" to see if you
have problems there.
Francisco Olarte.
/dev/shm, IIRC its mounted size is one limit for posix
shared memory.
Francisco Olarte.
your
server configuration and from the age in the mssages I suspect you
have the usual suspects debugged. But as you have a configuration
crahsing in minutes and it seems to be a dev server you could do it
easily.
Sorry.
Francisco Olarte.
ared memory limits are ok, especially if you upgraded something in
the OS when going from 9 to 15, which seems likely.
IIRC in linux you can read them in /proc/sys/kernel/shm*, and they
were configured via sysctl.
Francisco Olarte.
, you can have the data and some
transformations in a big sql file, but having some steps in a php file
will have them documented too. But if you want to do text processing
in SQL, go ahead, the only problems are going to be making it harder
to debug and probably harder to document. Now people know the signal
11 stuff and the problem can probably be tracked.
Francisco Olarte.
ll does the trick ).
Doing it with a filtering stream would allow you to easily process
gigabytes of data using a few megabytes of ram in the client. I'm not
sure about the server, but stream filtering lends itself to very easy
batching of copies, and from what I read your server is beefy.
Francisco Olarte.
s=> \timing
Timing is on.
s=> create temporary view tstview as select pg_sleep(1)::text;
CREATE VIEW
Time: 153.129 ms
s=> select * from tstview;
pg_sleep
--
(1 row)
Time: 1009.195 ms (00:01.009)
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Francisco Olarte.
On Mon, 4 Mar 2024 at 14:06, wrote:
> Am 04.03.2024 13:45 schrieb Francisco Olarte:
> > Intervals are composed of months, days and seconds, as not every month
> > has 30 days and not every day has 86400 seconds, so to compare them
> > you have to normalize them somehow, whic
1 year 360 days'::interval;
?column?
--
t
(1 row)
If you want to do point in time arithmetic, you will be better of by
extracting epoch from your timestamps and substracting that. Intervals
are more for calendar arithmetic on the type "set me a date two
months, three days and four hours from the last".
Francisco Olarte.
rom accidental modifications. Also, knowing your
transactions are not going to write make life easier for optimizers
and other things.
Francisco Olarte.
e a useful reference. You have not provided any and.
> in the 10-12MB/s throughput range.
This has the faint smell of a saturated 100Mbps link in the middle (12*8=96Mbps)
Make some tests of the network and measure ( and post them ) before,
so people can guesstimate something.
Francisco Olarte.
Lok:
On Sat, 3 Feb 2024 at 21:44, Lok P wrote:
> On Sun, Feb 4, 2024 at 12:50 AM Francisco Olarte
> wrote:
> However , as we have ~5billion rows in the base table and out of that , we
> were expecting almost half i.e. ~2billion would be duplicates. And you said,
> doing th
I'm not sure if ctid is ordered and you can select where
ctid>last ordered, if that works it probably is faster for immutable
origins.
Francisco Olarte.
t comes from cmd line or similar, if it comes from code just use
hton )
Francisco Olarte.
hout redirection?
Have you ruled out the usual suspect, stdout is line buffered when
going to a tty, full buffered when not ? ( by killing pg_revlogical
and/or insuring a long enough output is generated )
Francisco Olarte.
the line, but you have ellipsed-out ( is that
a word? ) the interesting names, so quoted vacuumdb line is useless
for check.
Francisco Olarte.
would be turning a stored procedure call into a prepared
statement for an inline procedure, but this is something else.
Francisco Olarte.
needs to be specified ).
feel free to exec &> /dev/null.
Francisco Olarte.
o the date math in the database too. Also, rhs of the
comparison seems to be date, if created_at is timestamp you may be
blocking the optimizer for some things.
Francisco Olarte.
c", the classic way to
start a slave ( controlled? helper ? I do not know the modern PC word
for that ) process on *ix, while Luca is thinking on a source fork,
the thing which is normally done in git and friends to have a similar
but derived software built.
Francisco Olarte.
2 ) maybe changing them to that helps ( but I do not have the
resources to test that on hand ).
Francisco Olarte.
s is not avalilable to the listener. On any
protocol. Things like http work because the clients send the dns alias
on some place on the default usage, but you can write an http client
which sends Host: from the uri given but connects to a different IP
address.
Francisco Olarte.
rge files it will take a lot
of space. But if you did it that way the copy of the data files will
not be in the initial ( empty ) copy of the datafiles, it will just be
in the wal copy, and it needs to be somewhere if you want to do that.
Francisco Olarte.
Hi karsten:
On Mon, 10 Apr 2023 at 11:40, Karsten Hilbert wrote:
>
> Am Mon, Apr 10, 2023 at 09:41:15AM +0200 schrieb Francisco Olarte:
>
> > On Mon, 10 Apr 2023 at 04:16, Rob Sargent wrote:
> > > > An UPSERT checks whether a row exists, if so, it does an update,
e to hold an id and not know whether or not it's already in
> the database.
This is extremely easy to do if you have natural instead of surrogate keys.
I work in telephony, upserting the last incoming call timestamp for a
phone number will be exactly that.
Francisco Olarte.
ures and the
virus scanner lines.
You can repeat a PUT request or not repeat it. HTTP request cover body
too, changing body is like changing URI, or changing method, it is a
different request. Idempotent in HTTP is same request, body included,
same response. Not similar request, similar response.
Francisco Olarte.
ed for things like:
insert into last_access(id,cuando) values (1,current_timestamp) on
conflict(id) set cuando=current_timestamp
insert into access_count(id, access_count) values (1,1) on
conflict(id) set access_count=access_count+1
Which are not idempotent ( and also frequent, I use both variants )
Francisco Olarte.
ollow
standards, and do something reasonable when not possible, but this
informix wart sounds particularly worthless to implement. Beside your
use case I do not think it would serve for anything else than
encouraging people to use an ill dessigned informix feature.
Francisco Olarte.
x read drops
to about 10Mb, plus the 1Mb range.
Also, I suspect you will have to read all unsummarized ranges (
probably before the summarized ones, as unsummarized can discard
summarizeds, but not the other way ).
Francisco Olarte.
ike a misfeature, like
needing DUAL to be more oracle-compatible.
Francisco Olarte.
t existed ).
Note: localhost is a name, like toro, and is resolved,
samehost/samenet/all are magic.
Sumarizing, I would:
- Use listen_adresses=*
- Use samehost in your rules.
Which will lead to a lean config which probably does what you want.
Francisco Olarte.
", which
gives the selected one:
$ ip route get 127.1.2.3
local 127.1.2.3 dev lo src 127.0.0.1 uid 1000
cache
Francisco Olarte.
Also, if you want it to work you would need a second hostssl line
listing localhost as the source address, or, IIRC, you can try to
force the source address for connections to be toro using some deep
magic jujus, as psql does not seem to suipport setting it.
Francisco Olarte.
you can recover a condition set on a
column. It is transformed to an equivalent. I'm not sure how it is
exactly done, but it probably also does whitespace normalization and
constant folding.
Francisco Olarte.
ing row, when it
fails it is gone. Search lists, google, the docs, its been explained
ad nauseam.
Francisco Olarte.
Ron:
On Thu, 4 Aug 2022 at 02:30, Ron wrote:
> DEFERRABLE
> NOT DEFERRABLE
> This controls whether the constraint can be deferred. A constraint that is
> not deferrable will be checked immediately after every command. Checking of
> constraints that are deferrable can be postponed until the end
Michael:
On Mon, 25 Jul 2022 at 12:01, Michael J. Baars
wrote:
> 2) I have three psql clients running, a version 12.6, a version 13.4 and a
> version 14.3. Until now a 'select * from table;' showed the output in 'less'
> or something alike and exited from 'less' when
> the output was complete.
at a time will end on the 501 char.
And probably PG can compare the strings in the shared buffers, so it
only needs some housekeeping information in work mem, and rely on its
infrastructure to bring the contents into shared buffers. I do not
think you are estimating memory usage right.
Francis
Hi Gavan.
On Wed, 20 Jul 2022 at 00:10, Gavan Schneider wrote:
> On 20 Jul 2022, at 4:08, Francisco Olarte wrote:
> As a remark, in Spain bill numbers need to be gapless increasing. I
> have done it with
>
> One answer to this problem has been around for a while, and my vers
able now for simplicity, I
doubt I gained enough performance to justify the 20-something perl
lines plus the crontab entry to use sequences. As beard turns grey I
apreciate more the simpler ways to do things.
Francisco Olarte.
, but rather you
inserting dates in timestamp columns and then substracting said
columns? Because even your operations are defined in terms of
timestamptz, not dates.
Francisco Olarte.
you can see if your
IDE/EDITOR/putty is plain nasty tricks ( if you tried pasting into cat
you can cat that file and the one from vim ).
Francisco Olarte.
n. Post more details and someone may
be able to help you.
Regards.
Francisco Olarte.
Hi Florents:
On Wed, 6 Jul 2022 at 12:35, Florents Tselai wrote:
> 50M+ rows and iirc pg_relation_size was north of 80GB or so.
Somebody already mentioned pg_table_size, but anyway this gives you
1.6Kb per row, which I would suspect is extremely low given your pdf
content and the 1M truncation y
e kind of query,
hi cpu would depend on what you call it, but it wouldn't surprise me
if it has at least one cpu running at full detoasting and doing
vectors, I do not know if alter table can go paralell..
Francisco Olarte.
On Fri, 10 Jun 2022 at 18:38, Rama Krishnan wrote:
>
> Hi All,
>
> I am want to delete old records using function so my senior has function like
> below but I want to get response of this particular inside query wheter it is
> successful or failure
> How to get response of the function status
On Fri, 13 May 2022 at 12:47, alias wrote:
> CREATE TABLE test_g (
> a timestamptz,
> b timestamp GENERATED ALWAYS AS (a::timestamp) STORED
> );
> then an error occurred.
>> ERROR: 42P17: generation expression is not immutable
Cast to timestamp uses current session time zone, current ses
Jerry:
On Fri, 6 May 2022 at 03:04, Jerry Sievers wrote:
> Has anyone run into This?
> Psycopg3 fails to resolve timezone localtime on MacOS (Catalina).
>
> It falls back to UTC regardless of whether running with/without the
> tzdata package which I did try.
>
> There is a /etc/localtime symlink
Replying to self...
On Wed, 4 May 2022 at 19:19, Francisco Olarte wrote:
> An I remember pg_dump had a catalog mode, but it seems to have
> dissapeared in recent versions ( you gave it a switch, it wrote a
> commented list
> of IDs which you could edit ( normally avoiding reorder
nt versions ( you gave it a switch, it wrote a
commented list
of IDs which you could edit ( normally avoiding reorders ) and feed
back with another switch to make partial backups, it was really useful
for selective
backups of complex stuff ).
Francisco Olarte.
e readline wizardry.
I've read joe has slave shell sessions. Other thing you could try (
I've done it with emacs shell mode ) is use that, but I fear it only
works well with single line queries. Or look if it has some kind of
sql modes ( interactive sql, not sql-script-syntax-highlight ).
Francisco Olarte.
ackets ) where intercepted by its REPL leading to
bizarre errors. And sometimes after a relogin things are done a bit
different and make problem disappear.
Francisco Olarte.
bs terminate with
newlines" leads to uglier/harder to read lines but can express them).
Francisco Olarte.
otes. For example:
"aaa","b CRLF
bb","ccc" CRLF
zzz,yyy,xxx
<<<
Which somehow contradicts 2.1.
In C/C++ it's easily parsed with a simple state machine reading char
by char, wich is one of the strong points of those languages, but
reading lines as strings usually leads to complex logic.
Francisco Olarte.
Hi Dominique:
On Tue, 11 Jan 2022 at 17:10, Dominique Devienne wrote:
...
> Creating the first two "fixed" schemas is fast enough. 300ms, including
> populating the 2nd with a bunch of rows.
> But creating the 3rd kind, which will be done many times, is not so fast, at
> 2.5 - 3s, which may see
Dominique:
On Tue, 11 Jan 2022 at 11:57, Dominique Devienne wrote:
> On Tue, Jan 11, 2022 at 11:05 AM Francisco Olarte
> wrote:
>> Not going to enter into the lock situation but...
> OK :). But it does matter. those locks. Especially if DROP'ing a DB (as you
> suggest)
atabase" approach ( using
create database template=, dropping the DB at the end ). It is fast,
it is simple, it is easy. Have you tried that? seems much
easier/faster than building and dropping all this
schemas/roles,specially for testing.
Francisco Olarte.
On Sun, 2 Jan 2022 at 20:42, ourdiaspora wrote:
> On Sunday, January 2nd, 2022 at 3:54 PM, Francisco Olarte
> wrote:
> > Inherits does not copy things. LIKE on creation does, but AFAIK it can
> > copy nearly everything except data.
> Thanks, had read that part of the docum
FAIK it can
copy nearly everything except data.
Francisco Olarte.
d buffers in the cache,
os, postgres, work mem, other processess and all sort of different
things eat ram. I would suggest looking at free/top/whatever too size
this ( it should not OOM, just distort pg estimates ).
Francisco Olarte.
Alan:
On Mon, 6 Dec 2021 at 18:58, Alan Hodgson wrote:
> On Mon, 2021-12-06 at 18:20 +0100, Francisco Olarte wrote:
> Can you post an explain analyze? To me it seems like the planner
> thinks shipment_import_id is randomly distributed and the table is
> well correlated with it'
all the table ). An explain analyze should show that ( or three, one
for that shipment import id, one for 1, one for a really big one )
> Just wondering if there's a knob I can turn to make these more likely to work
> without constantly implementing workarounds?
You may try a composite index.
Francisco Olarte.
wo queries plus
the third can be done in a single scan by capturing stats for home and
visitor in a single round, then sum it with itself with columns
swapped.
Also note, this is totally untested code and probably chock full of
typos and errors.
Francisco Olarte.
r keys in adequate chunks ) ( you can find adequate
bounds scanning the pkindex and skipping, just rememberto sue
half-open intervals and cover all the key domain ).
Francisco Olarte.
Ron:
On Fri, 15 Oct 2021 at 20:16, Ron wrote:
> > so no overlap.
> I was afraid you were going to say that. It's completely bizarre, but seems
> to be a "thing" in computer science.
Right half open intervals are normally choosed because they can fully
cover the real line without overlap. Full o
I just wanted to know if it's a know tradeoff of this new
> feature.
If you do not run the same sequences, you do not know. Note I do not
know what exact sequences you have tested, I write with only what I
have read as as input.
Francisco Olarte.
build;
> \di+ gist_fastbuild_pt_idx
> EXPLAIN (ANALYZE, BUFFERS) SELECT pt FROM gist_fastbuild WHERE pt <@
> box(point(.5,.5), point(.75,.75));
a couple times or more on each server to rule artifacts out.
( testing speed in general, which such fast times, I myself would
doubt comparisons of anyt
ll-101 question, not much to do with
postgres, you'll have to ask someone who knows your
os/shell/whatever.
Francisco Olarte.
es I've
used do it this way, when asked to match "g"lobally they do
non-overlapping matches, they do not substitute and recurse with the
modified string.
Also, your way opens the door to run-away or infinite loops (
rr('a','a','aa','g') or rr('a','a','a','g'), not to speak of
r('x','','','g') ). Even a misguided r(str, '_+','_','g'), used
sometimes to normalize space runs and similar things, can go into a
loop.
Francisco Olarte.
the value of next-contact at the
time the contact was made for analysis. This also makes finding
contacts to be made soon easier.
Of course no separate table for next-contact.
Francisco Olarte.
ngs but for task-like
things I use a pending and a done table, and the pending table bloated
a lot on the before-autovacuum times, small table nearly all dead
tuples, not a problem vaccuming it via cron every some minutes, as in
this case only the index is bloated autovacuum may not fire frequently
enough for it, as the table would have only 10k/30M~333 ppm bloat )
Francisco Olarte
Hi Thorsten:
On Thu, Jul 15, 2021 at 6:30 PM Thorsten Schöning wrote:
> I need to backup multiple host with multiple Postgres databases each.
> In all of those cases I'm interested to backup all databases, which
> makes pg_dumpall a natural choice at first glance. Though, from my
> understanding
epl is a function, it is called for every non-overlapping
occurrence of pattern. The function takes a single match object
argument, and returns the replacement string.
An so on on other languages, but in sql
regexp_replace ( string text, pattern text, replacement text [, flags
text ] ) → text
The replacement is a plain text ( and AFAIK you cannot use functions
as values in sql ).
You could probably define your function doing that if you have any PL
installed in your DB.
Francisco Olarte.
ad complains and I certainly do not try to make
people adopt any style, I just do tit for tat and go on. I care about
the quality of postings in this lists, I do not care at all about the
quality of mails I receive at work. My employer probably cares more,
as he pays me to read them and would like for me to spend as little
time as possible.
Francisco Olarte.
he spanish equivalent of high school in 1980 and although
there were photocopiers they were still used a lot. IIRC they were
much cheaper per copy, and were commonly used for exams and similar
high volume things. And in those years spain still lagged a lot behind
europe / usa.
Francisco Olarte.
is, as you pointed later, as bad or
worst than top.
I do not know if people realize a properly quoted message is easier on
the eye, and more likely to generate responses, especially when you
are requesting help.
Francisco Olarte.
And do not get me started on the "sent from my iPhone / yahoo
ger. So normally I stop reading at the quote and discard
the message if I have not understood with what I have read UNLESS it's
from my mother or I'm been paid to read it.
I find top-posting moderately offensive, like saying "I am not going
to waste time to make your reading experience better".
Francisco Olarte.
just 800/400 Mb.
You may want to investgate putting some more realistic data ( i.e.,
try 2/4/8 fields per row ) to gain some insight, and reading
https://www.postgresql.org/docs/current/storage-page-layout.html which
gives some pointers on where to go next.
Francisco Olarte.
()) - 15613200 > epok
or even reversed ( epok < EXTRACT(EPOCH FROM NOW()) - 15613200 )
I haven't tried, but if you some day index epok ( or already had ) the
pattern field-op-constant is normally more readily recognized by
optimizers ( probably they get it anyway ).
Francisco Olarte.
o logC comparison for element, so
add N*logC N(logN-logM). If you add appropiate constants and add all
you'll find the final result is O(NlogN).
Francisco Olarte.
Francisco Olarte.
Mitar:
On Fri, Apr 23, 2021 at 7:33 PM Mitar wrote:
> On Fri, Apr 23, 2021 at 10:28 AM Francisco Olarte
> wrote:
> > A fast look at the link. It seems to be long string of random LOWER
> > CASE HEX DIGITS. A simple huffman coder can probably put it in 5 bits
> &
Just a note:
On Fri, Apr 23, 2021 at 10:57 AM Mitar wrote:
> First, it is important to note that the JSON I am using contains
> primarily random strings as values, so not really something which is
> easy to compress. See example at [1].
A fast look at the link. It seems to be long string of rand
catalog
for the partition and then query the partition. ( In many years of
working with time-partitioned tables I have never got the need to
build a query to hit exactly X partitions from times ).
Francisco Olarte.
7;2020-10-01 00:00:00+00'::timestamptz), ('2020-10-01
00:00:00+00'::timestamptz at time zone 'utc'+ interval '1 month') at
time zone 'utc';
timestamptz |timezone
+
2020-10-01 02:00:00+02 | 2020-11-01 01:00:00+01
(1 row)
* Now same UTC hours, different local. ( 02 + 02, 01 + 01 )
If I had monthly partitions rolling at 00 utc, 1st one will nibble the
first hour of an extra one.
TSTZ is hard.
Francisco Olarte.
onds )
- When adding, IIRC, first add the months, then add the days, then add
the seconds, rollig over the date as needed in each step.
The very few cases where I've used it, mainly for calendaring, or for
partitioning ( which is calendaring ), has worked well. Things like
"meeting sche
1 - 100 of 233 matches
Mail list logo
