ata 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.
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
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.
eful 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.
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.
line, but you have ellipsed-out ( is that
a word? ) the interesting names, so quoted vacuumdb line is useless
for check.
Francisco Olarte.
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.
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.
assic 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.
) maybe changing them to that helps ( but I do not have the
resources to test that on hand ).
Francisco Olarte.
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 upda
come 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.
rus 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.
nsert 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.
d 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.
eature, like
needing DUAL to be more oracle-compatible.
Francisco Olarte.
ote: 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.
ich
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.
ine
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.
es 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.
, 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
or 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.
tamp columns and then substracting said
columns? Because even your operations are defined in terms of
timestamptz, not dates.
Francisco Olarte.
/putty is plain nasty tricks ( if you tried pasting into cat
you can cat that file and the one from vim ).
Francisco Olarte.
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
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
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
ve 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.
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.
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.
minate with
newlines" leads to uglier/harder to read lines but can express them).
Francisco Olarte.
ple:
"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
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) requ
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 do
rly everything except data.
Francisco Olarte.
ared 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's PK,
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.
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.
nd 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
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.
tbuild_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 anything but exact runs )
It
a shell-101 question, not much to do with
postgres, you'll have to ask someone who knows your
os/shell/whatever.
Francisco Olarte.
s, 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.
capture 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.
s 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
>
guages, 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.
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.
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 mail
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.
parison 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
never got the need to
build a query to hit exactly X partitions from times ).
Francisco Olarte.
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.
very few cases where I've used it, mainly for calendaring, or for
partitioning ( which is calendaring ), has worked well. Things like
"meeting scheduled '3 months' from previous, confirmation mails sent
'3 months, -14 days' from previous'. I've never tried to make some
thing as comp
epoch to convert timestamptz to a simple number of
seconds, do arithmetic there (where intervals are just plain numbers )
and convert back using to_timestamp().
It seems you expect a different behaviour from intervals to what they
are dessigned for.
Francisco Olarte.
gs cached. This seems to be
the kind of problem where the generic solution is hard but a little
insider knowledge can accelerate it a lot.
Regards.
Francisco Olarte.
are always there after DB creation
and are normally needed for the server to work ) OBJECTS. Also,
naming an schema "sys" does not make it a system schema.
Francisco Olarte
On Wed, Feb 24, 2021 at 12:12 PM Francisco Olarte
wrote:
> AFAIK dt list "tables", not "tables in the schemas in search path".
> It states " By default, only user-created objects are shown; supply a
> pattern or the S modifier to include system objects."
ault, only user-created objects are shown; supply a
pattern or the S modifier to include system objects.", but these sys
schema does not seem to be a system one.
Francisco Olarte.
t;, this may be chosen or not for
queries. You should be the one more probable to know it.
Francisco Olarte.
Thorsten:
On Wed, Feb 10, 2021 at 12:36 PM Thorsten Schöning
wrote:
> Guten Tag Francisco Olarte,
> am Mittwoch, 10. Februar 2021 um 10:38 schrieben Sie:
> > [...]and partitioning sorts partially
> > by date ( or fully if you have made the partitions by range-querying
rtitioning. IIRC cluster will do the trick
if it is indexed.
( This has happened to me compressing document dumps, presorting by
some chosen fields improved my compression ratio a bit, IIRC it was
due to the compressor searching for duplicates on a limited window
only, this is why I use "detectable" redundancy )
Francisco Olarte.
Rich:
On Sun, Nov 1, 2020 at 8:04 PM Rich Shepard wrote:
>
> On Sun, 1 Nov 2020, Francisco Olarte wrote:
>
> > This is nearly impossible to diagnose without a chunk of the query (
> > i,e, the first lines and the last ones ).
> Francisco,
> $ psql
If it matches something
above you've got it.
But really, show your code, otherwise you are not going to get much help.
Francisco Olarte.
test1 as t1 wher t1.v1 not in ( select t2.v1 from
test2 as t2 )
Would have caught it.
Francisco Olarte.
r index than 16
byte uuid.
Francisco Olarte.
nicode codepoints instead of ASCII
chars as units you could probably use more creative encodings ;-) ,
but probably using a "drop the dashes" subst in the interface will be
your simpler option.
Francisco Olarte.
When Access does it the database is in postgres, or swl server, or
jet, or whatever. Access is not a database, in the sense postgres it.
Jet is.
Francisco Olarte.
great circle.
Francisco Olarte.
oint of access to the DB
mediating every query/operation with a stored procedure/function,
using accounts with access to only those, even for selects, so you
have tight control and easier auditing.
Francisco Olarte.
e links which can be provided.
Bear in mind that for all we know you could be connecting using your
own gopher based tool which translates coloquial swahili to sql.
Francisco Olarte.
goes.
The PK stuff is bulk-loading 101. Try explain AND explain analyze of
some variants, remember to analyze your tables ( seems redundant, but
the PK & redundant hash key stuff leads me to think you are not too
experienced on postgres usage ).
Francisco Olarte.
here are tons of info floating around, more or less
easy to find.
> Serialize is the solution to this issue.
That's what locks and isolation levels do.
Francisco Olarte.
1 - 100 of 205 matches
Mail list logo
