perl -w -e 'printf q(%1$f %1$.20f %1$a %1$.20a)."\n", 0.1'
0.10 0.1555 0x1.ap-4 0x1.a000p-4
See the trailing 555 in the second? or the continous 1.(9) fraction
rounded to a in excess on the least significant place in hex?
In numeric you can hace 1.0, 1.0, 1.1
and 0.99, but in doubles they all map to
1.0
Francisco Olarte.
Melvin:
On Thu, Mar 1, 2018 at 1:47 AM, Melvin Davidson wrote:
> But this begs the question,
> why are "developers" allowed to design database tables? That should be the
> job of the DBA!
That's the DBA wearing her developer hat. ( I agree with the spirit )
Francisco Olarte.
s with an opposite
> transaction.
And normally you would need to pinpoint an individual transaction for
selection, hard to do if you do not have a pk.
Francisco Olarte.
conds after deployment the PHB will decide you can
do cross-currency sales.
Francisco Olarte.
atabase).
Good point. All the times I've found myself with complete duplicates
allowed I've alwasy found the correct model is no duplicates + count
field ( with possible splits as you pointed below ). I would not have
a "marbles" table with (red, red, blue, white, red, white), I would
switch it to red=3, blue=1, white=2.
Francisco Olarte.
ained in each row. Anyway,
it is easy with a procedure, look for example at
https://www.postgresql.org/docs/10/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN
just do a loop on your command table, follow the examples and you
should be done.
Francisco Olarte.
PS:
> Jacek
I
unction which does it for you, you'll have to code something, and I
suspect it may be easier to just capture op, old and new in your triggers
and use an external program to build the inverted queries.
Francisco Olarte.
y better ( is
like backups, you normally make backups faster, then optimize what you
can of restores, as ideally they would never be used, or transactions,
you normally optimize for commits first, then rollbacks ).
Francisco Olarte.
Jacek:
On Thu, Mar 8, 2018 at 1:53 PM, Łukasz Jarych wrote:
> maybe somebody wrote SP to invert update statement?
Maybe, but if you are going to ask for it you better define the
problem a little.
Francisco Olarte.
ard from the last full backup
to just before the delete.
Francisco Olarte.
ession configured time zone, in the without case it treats it (
aproximately ) as if it was in utc ( and then discards the "+00" after
formating ).
Maybe I'm confussing you more, its not too easy to explain.
The point is TIMEZONE is not stored in either of them.
Francisco Olarte.
On Thu, May 10, 2018 at 12:19 PM, Ben Hood wrote:
>> On 10 May 2018, at 09:59, Francisco Olarte wrote:
>> Maybe I'm confussing you more, its not too easy to explain.
>> The point is TIMEZONE is not stored in either of them.
>
> Many thanks for clarification, ve
ere
Good bye then
folarte:~/tmp$ perl -e 'exec("./xx")'
#!/bin/cat
Hello there
Good bye then
You can try other ways to call execv*, nothing magical in the perl
way, just avoiding the shell ( which has an exec builtin command, with
different behaviour from typing a command name, which does fork, wait
in the parent, execv in the child ).
Francisco Olarte.
On Thu, May 10, 2018 at 10:37 PM, Ben Hood wrote:
> On 10 May 2018, at 16:33, Francisco Olarte wrote:
>
> For what you want to do I think you'll have to parse the text value,
> maybe by definig a view with a text columns and using some
> rule/trigger magic for insert / upda
Adrian:
On Sat, May 12, 2018 at 6:42 PM, Adrian Klaver
wrote:
> On 05/12/2018 04:04 AM, Francisco Olarte wrote:
...
>> Can you post an example ( correct, error inputs, preferrably done with
>> psql ) ?
>>
>> At this point I do not know if it is working or if it see
with a timestamp with timezone that you
cannot do with a int8 number or a with a timestamp without time zone.
Given there is an isomorphism between them ( and, in postgres, the
text version is the same for the inverse, IIRC "one=the_other at
timezone UTC" ) and they have the same representation, I doubt it.
They are just convenient ways to manipulate text values and apply
functions based on type, but they do not have a time zone.
You can not get a time zone from a timestamp with time zone value.
What leads to countless confussion is it seems that a tstz-value
should be composed of a ts-value plus a tz-value, but they are not. Is
just a problem with naming.
Francisco Olarte.
rstand, but
CASE WHEN condition THEN true ELSE false END
when contition is ( as it should in a CASE ) a boolean expression is a
classic antipattern , just use 'condition' or '(condition)'
Francisco Olarte.
r_id_column=$<")
( Of course, you could "use English;" to get $UID, or use placeholders
to avoid injections, that's just an initial pointer )
( or "perldoc perlvar" if you do not know what $< is, and that is
enough perl stuff for a postres list )
Francisco Olarte
27;68/1225BB70') - pg_lsn('0/0'))::bigint)
;
( http://sqlfiddle.com/#!17/9eecb/16272 )
Reconstructing via simple addition does not work, but you can do
division, modulus, double to_hex, join with '/', cast to pg_lsn if you
like.
Francisco Olarte.
he event
and notifying selectively.
You can use a channel per event.
Francisco Olarte.
need C extension functions anyway, unless your usage
pattern is truly bizarre a triger for ddl could be written in any pl.
Francisco Olarte.
me listen fires, it seems the difficult work is
done but it misses the notify to signal listeners and avoid having to
rescan on a timer or a similar thing ).
( Maybe I missed the line, it would not be the first time, that's why I ask ).
Francisco Olarte.
e things ( like dates, which are
countable ) it does not matter that much, I use half-open for easier
upgrading if I need to, but you can translate open-closed-half.
Francisco Olarte.
ot;, and also
some conditions ( i.e., can the small periods interesect? are they
completely covered by the big one? can they include the endpoints of
the big one? Can they be empty? Does the result need to be minimal (
in the number of result periods ) ? ).
Even then, this is not trivial and, as said above, you will probably
need a function for it.
Francisco Olarte.
ponse to it a
violation of the current CoC ?
Francisco Olarte.
advice previously,
and like those I only know ways to do it daily/weekly/monthly/yearly.
Francisco Olarte.
more stuff, I
think the function will also need read-commited or something similar
to see the data appear, and fail under serializable. Pattern certainly
smells funny. I do some similar things, but I sleep outside of the
database, is there a reason this can not be done?
Francisco Olarte.
n a loop ), and a
wrapping one testing for the "data appears" condition and calling the
first, and call the second in a loop. But again, details will be
needed and trusting your words I do not want them on the list ;-> .
Francisco Olarte.
D copy in the index. So, getting it from the
server is trivial for anyone with filesystem access.
Francisco Olarte.
FULL JOIN temperatures USING ("time")
ORDER BY 1; -- ORDER BY GOES HERE, I suspect your example got ordered
by chance, not by dessign.
( use other names, I just used the view names for C&P, lazy me ).
Francisco Olarte.
o compress
network traffic between a pg windows client and a windows server on
another machine" and some windows savvy reader may be able to help you
( I can think of a couple solutions for that on my basic desktop OS
with the basic tools, I'm sure something like windows must have dozens
).
Francisco Olarte.
LL=en_US.UTF-8 sort
a
A
b
B1
$ echo -e 'a\nA\nb\nB1' | LC_ALL=C.UTF-8 sort
A
B1
a
b
Because it may matter for you, and I doubt the interactions can be
properly tested without a proper dataset AND a windos pg server.
Francisco Olarte.
s not ignore _ as the windows one does is easy, but
replicating all the doodahs may be difficult without knowing how the
windows locale sorts all the chars ( and I'm not getting at 1a vs a1,
or _ vs a vs A).
I suspect C locale may be ok though.
Francisco Olarte.
to reject an entry if b is null, because I
dessigned it that way, but DOES REJECT if a is null and B is not.
Regards.
Francisco Olarte.
e a little care when mutating them, you can do it traversing by id,
but you need a full scan, a recursive query with several index scans
may easily be faster in wide trees.
Francisco Olarte.
and even doing "cat *.sql | psql" and naming the
scripts appropiately. You'll probably have it easier going by that
route ( generating the sql script from chunks using a tool of your
choice, it's just a big text chunk and you can easily debug it with a
text editor ), and it is not that complex.
Francisco Olarte.
aux files + source some echoing files and, if quotes
are used for something like column names, cat + here docs.
Francisco Olarte.
9.11.20 20:00:00'::timestamptz) -
extract(epoch from '2019.06.10 10:00:00'::timestamptz) as
elapsed_seconds;
elapsed_seconds
-
14122800
(1 row)
Francisco Olarte.
OOps, I got it bad:
On Thu, Sep 12, 2019 at 1:50 PM Francisco Olarte wrote:
> timestamp / interval arithmetic is really a hairy thing. ( As shown
> below, start point carefully taken to avoid crossing dsts )
It was chosen to FORCE, not AVOID, crossing dst.
> cdrs=# select x, '20
Adrian:
On Wed, Sep 11, 2019 at 11:19 PM Adrian Klaver
wrote:
> On 9/11/19 9:34 AM, Francisco Olarte wrote:
...
> > If you want to know the elapsed minutes between two timestamps, it
> > might be better to do it directly, extract the epoch from both (
> > seconds ), su
alls to extract ring / talk
time from setup/connect/disconnect time, and I do not hit problems
because I never do interval arithmetic on elapsed times , its not a
problem if you are aware that there are "infinite" ways to represent
an epoch in an interval .
Francisco Olarte.
populate prev_stage_id, and index it and you can query next stage of a
tuple using it.
Francisco Olarte.
ge stage lists, splicing things
around ), I've normally found it's easier, in sql, to model this kind
of thing with a master-detail + order column.
( whatever = (id, , first_stage_id), stages=(id, order, ) )
Francisco Olarte.
ral data structures, SQL is
declarative, so they are not a great match, that's one of the reasons
why they are rarely seen. Things like master-detail have less
impedance mismatch.
Francisco Olarte.
1234ABCD"),"\n")'
3132333441424344
$ perl -e 'print(unpack("H*", "día año"),"\n")'
64c3ad612061c3b16f
Francisco Olarte.
sing
huge texts and extracting some data in C. But utf-8/latin-1, you're
right, you have umlauts, we have accendts and n-tildes, I've been
there and will be again. Enough off-topic anyway.
Francisco Olarte.
-SETTINGS
, although being lazy I just searched for "listen" on the manual index
)
Francisco Olarte.
--
> 22GB
...
> max_worker_processes
> --
> 8
I may be mislead, but isn't 16Gb a little spartan for 24 CPU with 8
workers ( per query ? ). Also, I assume the 22Gb is because you are
accounting for a huge host cache.
Francisco Olarte.
names
aka dir contents (not being able to do this is one of my strongest
dislikes of windows, it makes so many things so much simpler ).
Francisco Olarte.
er of these are C, they COULD be C++
> // Begin the cursor
Same as this comment.
> PQexec(connection, ,"BEGIN"))
> PQexec(connection, ,"myFetchSql”)
And these are definitely not C ( no ; ) and, if you generated them by
editing, myfetchsql is quoted which
/logs of the postgres installed machine.
... Some more things followed by a huge bottom quote-to-the-sig.
Your writing style makes it really hard to decipher what you are
trying to say. I hope your problem has been solved, because I am not
going to try to decipher it or comment more.
Francisco Olarte.
SERVER user ( as psql is separated, it communicates by a
socket, can be in another machine ).
Francisco Olarte.
move data around, in single transactions
so you do not lose anything, like shaking a ballot box periodically (
but ensure the lid is properly taped first ).
Francisco Olarte.
set it to true in the handle, put
all your code in a sub() and catch it, in programs where you donot
have easy recovery of errors, and use local eval if needed to catch
this kind of prim. key violation things.
Francisco Olarte.
lass);
to get a direct boolean and benefit from shortcircuiting, by putting
the most likely one first, and from the internal knowledge the
optimizer may have to not fully evaluate queries, which may be greater
than deducting from the union/limit case.
Francisco Olarte.
Other
transactions may read them, which is why readers do not block writers,
but two updates on the same tuple serialize.
Francisco Olarte.
ere. But there 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.
ill try just select name1 from games to see how it 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.
r LOBs, but if you do there
are 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.
apps a single point 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.
re 5500 km over the great circle.
Francisco Olarte.
o it.
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.
ses unicode 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.
denser index than 16
byte uuid.
Francisco Olarte.
st1 as t1 wher t1.v1 not in ( select t2.v1 from
test2 as t2 )
Would have caught it.
Francisco Olarte.
ding one BEFORE the semicolon. 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.
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 -d bustrac -f loc
use for partitioning. 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.
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
s may be chosen or not for
queries. You should be the one more probable to know it.
Francisco Olarte.
, 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.
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.",
les, which 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
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.
bs terminate with
newlines" leads to uglier/harder to read lines but can express them).
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.
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.
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.
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
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
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
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
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.
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
n. Post more details and someone may
be able to help you.
Regards.
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.
, 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.
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.
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
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
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.
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
ing row, when it
fails it is gone. Search lists, google, the docs, its been explained
ad nauseam.
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.
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.
", 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.
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.
ike a misfeature, like
needing DUAL to be more oracle-compatible.
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.
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.
1 - 100 of 234 matches
Mail list logo
