Re: Enforcing uniqueness on [real estate/postal] addresses
On 2020-05-12 21:55:56 +0100, Peter Devoy wrote: > >Is is possible to have two entries which have the same > >address_identifier_general, street and postcode, but different > >descriptions? > > Unfortunately, yes. The data comes from gov't systems to > regulate the development/alteration of arbitrary pieces of property and > those pieces do not always have a postal address. E.g. a farmer may > one year apply to erect a wind turbine in "field north of Foo Cottage" > and the next year apply to demolish "barnhouse west of Foo Cottage". I see. So postcode, street, address_identifier_general, description (from least to most specific) together identify an object. Going back to your original question I think that in this case it is actually useful to distinguish between NULL (unknown) and '' (empty), and if unknown values are forbidden, enforce that with a non null constraint. Consider the following examples: postcode | street | address_identifier_general | description 1234 | main street | 12 | '' 1234 | main street | 12 | NULL 1234 | main street | NULL | bike shed 2345 | '' | 12 | '' The first one refers to the whole property at main street 12. The second one maybe only to a part of it but we don't know which one. In the third example tghe address_identifier_general is unknown. Some bike shed on main street, There might be more than one, so PostgreSQL is correct not to enforce the unique constraint. In the last one there is no street name - it's not unknown, we know that there is none because this is a small village which doesn't have street names, just house numbers. hp -- _ | Peter J. Holzer| Story must make more sense than reality. |_|_) || | | | h...@hjp.at |-- Charles Stross, "Creative writing __/ | http://www.hjp.at/ | challenge!" signature.asc Description: PGP signature
Re: Column reset all values
On 2020-05-14 16:32:41 +0400, otar shavadze wrote: > also nor index drop is an option, because I need re-create index as I use this > table in procedure, so index is necessary for further queries. So total > runtime will not decreased. A full index on a column where all the values are the same (NULL in this case) is useless. For querys which check for the existing value, the optimizer will notice that a full table scan is faster. For query which use any other value, you expect 0 results: So all the optimizer want to know is that the value is indeed not in the column so that it can skip the table entirely. You can do that with a partial index (WHERE col IS NOT NULL) or maybe even a constraint. So I would drop the full index, update the table and then create a partial index. hp -- _ | Peter J. Holzer| Story must make more sense than reality. |_|_) || | | | h...@hjp.at |-- Charles Stross, "Creative writing __/ | http://www.hjp.at/ | challenge!" signature.asc Description: PGP signature
Pgpool is crashing when terminating user session
Hello all, We are having master-slave setup with pgpool pointing, only to master server. Whenever i tried to terminate the long running session on db end using SELECT pg_terminate_backend(pid), pgpool is getting crashed. Many blogs are saying this is the expected behaviour of pgpool, but my question is there anyway to terminate unwanted sessions on db without loosing other connections. Because restarting entire system every time for a single trouble causing session is a big hectic for us. Please share you ideas on this. Thanks & Regards, Rajamohan.J
Re: schema agnostic functions in language sql
> On >>> regards, tom lane >> Did my message with a sql and plgpsql versions not come through? >> I cannot create a plain sql function unless the search_path covers any table >> mentioned. Not the case when using plpgsql - no path needed. > > But does the plpgsql segment_calls() run? > > On other words does: > > select * from segment_calls(segid uuid); > > work? > >> I'm ok(ish) with that, unless I've missed some detail. >> rjs > Yes the plpgsql form works but of course requires a sufficient search_path > > -- > Adrian Klaver > adrian.kla...@aklaver.com
Re: Pgpool is crashing when terminating user session
> Hello all, > > We are having master-slave setup with pgpool pointing, only to master > server. Whenever i tried to terminate the long running session on db end > using SELECT pg_terminate_backend(pid), pgpool is getting crashed. Many > blogs are saying this is the expected behaviour of pgpool, but my question > is there anyway to terminate unwanted sessions on db without loosing other > connections. > > Because restarting entire system every time for a single trouble causing > session is a big hectic for us. Please share you ideas on this. Those blogs are incorrect. From Pgpool-II 3.6 Pgpool-II supports pg_terminate_backend(). Are you sure that you use "SELECT pg_terminate_backend(pid)" from Pgpool-II session, not from a session directly connecting to PostgreSQL? Port 11000 is the port Pgpool-II is listening on. [killing session] $ psql -p 11000 test psql (12.2) Type "help" for help. test=# select pg_terminate_backend(13877); pg_terminate_backend -- t (1 row) [killed session] $ psql -p 11000 test psql (12.2) Type "help" for help. test=# select pg_sleep(600); FATAL: terminating connection due to administrator command ERROR: unable to forward message to frontend DETAIL: FATAL error occured on backend server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Succeeded. test=# Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese:http://www.sraoss.co.jp
Re: schema agnostic functions in language sql
> On May 15, 2020, at 6:03 PM, Tom Lane wrote: > > Rob Sargent writes: >> I cannot create a plain sql function unless the search_path covers any >> table mentioned. Not the case when using plpgsql - no path needed. > > Oh, one of the things that's quite a lot different is the checking > applied at function creation time ;-). > > For a SQL function, by default we'll try to parse and analyze the body, so > any unknown tables will draw an error. plpgsql doesn't go further than a > very crude syntax check. > > If you don't like that, you can set check_function_bodies = off while > creating your SQL functions. But in any case, it's only related to what > happens at execution if the search path is the same. > >regards, tom lane And my fundamental error was thinking the parse of all create function calls was not language specific beyond syntax. Looking back, my use of sql functions has been for inline-able calculations reused in other plpgsql functions. check_function_body=off may be what I want during the site install as the definitions should be correct in all aspects. Thank you all rjs
Re: Inherited an 18TB DB & need to backup
> O >> Another problem is storage devices fail. S3 storage lakes _should_ be >> checking your data integrity on a regular basis and possibly maintaining >> copies of it iin multiple locations so you're not vulnerable to a site >> disaster. > > Tape FTW!! > > -- Or WTF Tape?? :) > Angular momentum makes the world go 'round.
Re: Circles with circle() vs ST_Buffer() Equality equalities
Hello,
Sorry, forget my questions.
I checked the differences between the 2 polygons too quickly.
Actually, I checked only some points and some attributes :
SELECT ST_Area(geometry), ST_Perimeter(geometry),
ST_NPoints(ST_ExteriorRing(geometry))
FROM (
SELECT polygon(32, circle('POINT(1 2)'::geometry::point, 0.5::double
precision))::geometry
UNION
SELECT ST_Buffer('POINT(1 2)'::geometry, 0.5::double precision)
) t(geometry)
;
st_area | st_perimeter | st_npoints
---+--+
0.780361288064513 | 3.13654849054594 | 33
0.780361288064513 | 3.13654849054594 | 33
--> exactly the same results with the 2 polygons
...
--> But, NOT all the points are identical :
SELECT ST_AsText((g1).geom) FROM (
SELECT st_dumppoints(ST_ExteriorRing(geometry))
FROM (
SELECT polygon(32, circle('POINT(1 2)'::geometry::point, 0.5::double
precision))::geometry
) t1(geometry)
)t11(g1)
EXCEPT
SELECT ST_AsText((g2).geom) FROM (
SELECT st_dumppoints(ST_ExteriorRing(geometry))
FROM (
SELECT ST_Buffer('POINT(1 2)'::geometry, 0.5::double precision)
) t2(geometry)
)t22(g2)
ORDER BY 1 ;
st_astext
---
POINT(0.509607359798385 1.90245483899194)
POINT(0.509607359798385 2.09754516100806)
POINT(0.538060233744357 2.19134171618254)
POINT(0.584265193848727 1.7222148834902)
POINT(0.584265193848727 2.2777851165098)
POINT(0.646446609406726 1.64644660940673)
POINT(0.646446609406726 2.35355339059327)
POINT(0.722214883490199 1.58426519384873)
POINT(0.722214883490199 2.41573480615127)
POINT(0.808658283817455 1.53806023374436)
POINT(0.808658283817455 2.46193976625564)
POINT(0.902454838991936 1.50960735979838)
POINT(0.902454838991936 2.49039264020162)
POINT(1 2.5)
POINT(1.46193976625564 2.19134171618254)
POINT(1.49039264020162 2.09754516100806)
(16 lignes)
SELECT ST_AsText((g1).geom) FROM (
SELECT st_dumppoints(ST_ExteriorRing(geometry))
FROM (
SELECT ST_Buffer('POINT(1 2)'::geometry, 0.5::double precision)
) t1(geometry)
)t11(g1)
EXCEPT
SELECT ST_AsText((g2).geom) FROM (
SELECT st_dumppoints(ST_ExteriorRing(geometry))
FROM (
SELECT polygon(32, circle('POINT(1 2)'::geometry::point, 0.5::double
precision))::geometry
) t2(geometry)
)t22(g2)
ORDER BY 1 ;
st_astext
---
POINT(0.509607359798384 2.09754516100806)
POINT(0.509607359798385 1.90245483899193)
POINT(0.538060233744356 2.19134171618254)
POINT(0.584265193848726 2.2777851165098)
POINT(0.584265193848728 1.7222148834902)
POINT(0.646446609406725 2.35355339059327)
POINT(0.646446609406727 1.64644660940673)
POINT(0.722214883490197 2.41573480615127)
POINT(0.7222148834902 1.58426519384873)
POINT(0.808658283817453 2.46193976625564)
POINT(0.808658283817456 1.53806023374436)
POINT(0.902454838991934 2.49039264020161)
POINT(0.902454838991937 1.50960735979838)
POINT(0.998 2.5)
POINT(1.46193976625564 2.19134171618255)
POINT(1.49039264020161 2.09754516100807)
(16 lignes)
=> It is a bit weird, but it seems there are slight differences on some points.
For example :
POINT(1 2.5) for polygon(32, circle('POINT(1 2)'::geometry::point, 0.5::double
precision))::geometry
and
POINT(0.998 2.5) for ST_Buffer('POINT(1 2)'::geometry, 0.5::double
precision)
I guess it comes from rounding because the start point is not the same for the
2 polygons.
- Météo-France -
PALAYRET JACQUES
DCSC/MBD
jacques.palay...@meteo.fr
Fixe : +33 561078319
Re: Inherited an 18TB DB & need to backup
On 2020-05-15 14:02:46 +0100, Rory Campbell-Lange wrote: > On 15/05/20, Suhail Bamzena (suhailsa...@gmail.com) wrote: > > I have very recently inherited an 18 TB DB that is running version 9.2. > > Apparently this database has never been backed up [...] > A very simple solution could be just to dump the database daily with > pg_dump, if you have the space and machine capacity to do it. Depending > on what you are storing, you can achieve good compression with this, and > it is a great way of having a simple file from which to restore a > database. > > Our ~200GB cluster resolves to under 10GB of pg_dump files, although > 18TB is a whole different order of size. I love pg_dump (especially the -Fd format), but for a database of that size it might be too slow. Ours is about 1TB, and «pg_dump --compress=5 -Fd» takes a bit over 2 hours. Extrapolating to 18 TB that would be 40 hours ... And restoring the database takes even more time because it only restores the tables and has to rebuild the indexes. Still - for a first backup, just firing off pg_dump might be the way to go. Better to have a backup in two days than still none after two weeks because you are still evaluating the fancier alternatives. hp -- _ | Peter J. Holzer| Story must make more sense than reality. |_|_) || | | | h...@hjp.at |-- Charles Stross, "Creative writing __/ | http://www.hjp.at/ | challenge!" signature.asc Description: PGP signature
Re: Inherited an 18TB DB & need to backup
Hi Peter Thanks for the info & the entire forum for their inputs i did fireup a pg_dump last night pairing it with gzip & split it to 1TB size.. will let you all know how it goes. On Sat, 16 May 2020, 18:12 Peter J. Holzer, wrote: > On 2020-05-15 14:02:46 +0100, Rory Campbell-Lange wrote: > > On 15/05/20, Suhail Bamzena (suhailsa...@gmail.com) wrote: > > > I have very recently inherited an 18 TB DB that is running version 9.2. > > > Apparently this database has never been backed up > [...] > > A very simple solution could be just to dump the database daily with > > pg_dump, if you have the space and machine capacity to do it. Depending > > on what you are storing, you can achieve good compression with this, and > > it is a great way of having a simple file from which to restore a > > database. > > > > Our ~200GB cluster resolves to under 10GB of pg_dump files, although > > 18TB is a whole different order of size. > > I love pg_dump (especially the -Fd format), but for a database of that > size it might be too slow. Ours is about 1TB, and «pg_dump --compress=5 > -Fd» > takes a bit over 2 hours. Extrapolating to 18 TB that would be 40 hours > ... > > And restoring the database takes even more time because it only restores > the tables and has to rebuild the indexes. > > Still - for a first backup, just firing off pg_dump might be the way to > go. Better to have a backup in two days than still none after two weeks > because you are still evaluating the fancier alternatives. > > hp > > -- >_ | Peter J. Holzer| Story must make more sense than reality. > |_|_) || > | | | h...@hjp.at |-- Charles Stross, "Creative writing > __/ | http://www.hjp.at/ | challenge!" >
Re: schema agnostic functions in language sql
On Sat, May 16, 2020 at 5:15 AM Rob Sargent wrote: > check_function_body=off may be what I want during the site install as the > definitions should be correct in all aspects. > You should probably just have one "test" schema and compile your functions with the non-client test schema in the search_path. David J.
Removing Last field from CSV string
Hi,
I have a string that I want to cut to 60 char and then remove the last
field and comma.
substring('Class V,Class VI,Class VII,Competitive Exam,Class VIII,Class
X,Class XI,Class IX,Class XII',1,60);
substring | Class V,Class VI,Class VII,Competitive Exam,Class VIII*,Class*
Now I try to remove the last field and comma ",Class"
To get Class V,Class VI,Class VII,Competitive Exam,Class VIII
Is there a function or easy way to do this?
Any help would be appreciated.
Thank you
Alex
Re: schema agnostic functions in language sql
> On May 16, 2020, at 9:13 AM, David G. Johnston > wrote: > > >> On Sat, May 16, 2020 at 5:15 AM Rob Sargent wrote: >> check_function_body=off may be what I want during the site install as the >> definitions should be correct in all aspects. > > You should probably just have one "test" schema and compile your functions > with the non-client test schema in the search_path. > > David J. In fact there is just such a thing in the database from which get the DDL to generate new ‘client’ dbs. (This is not commercial in any way). So far I have not needed to bring that template into client space. Do you see a problem with using check_ function_body=off, given that the functions will be developed and tested elsewhere?
Re: Removing Last field from CSV string
Hello,
Perhaps, a statement like :
substring(theString, 1, length(theString)-position(',' IN reverse(theString)))
with theString 'Class V,Class VI,Class VII,Competitive Exam,Class VIII*,Class'
for example.
Regards
- Météo-France -
PALAYRET JACQUES
DCSC/MBD
jacques.palay...@meteo.fr
Fixe : +33 561078319
Re: Removing Last field from CSV string
On 5/16/20 9:31 AM, PALAYRET Jacques wrote:
Hello,
Perhaps, a statement like :
substring(theString, 1, length(theString)-position(',' IN
reverse(theString)))
with theString 'Class V,Class VI,Class VII,Competitive Exam,Class
VIII*,Class' for example.
That's cool. I did a little fiddling with above:
SELECT
substring(
left('Class V,Class VI,Class VII,Competitive Exam,Class VIII,Class
X,Class XI,Class IX,Class XII', 60), 1, length(
left('Class V,Class VI,Class VII,Competitive Exam,Class VIII,Class
X,Class XI,Class IX,Class XII', 60)) - position(',' IN reverse(
left('Class V,Class VI,Class VII,Competitive Exam,Class VIII,Class
X,Class XI,Class IX,Class XII', 60
substring
Class V,Class VI,Class VII,Competitive Exam,Class VIII
Regards
- Météo-France -
PALAYRET JACQUES
DCSC/MBD
jacques.palay...@meteo.fr
Fixe : +33 561078319
--
Adrian Klaver
adrian.kla...@aklaver.com
template0 needing vacuum freeze?
PG 9.6 (9.6.8). Got an alert today when template0 and template1 were both
over 50% towards TXID wraparound. I could vacuum template1 but couldn't
vacuum template0 without first allowing connections. This is what it looked
like before:
# SELECT datname
, age(datfrozenxid)
, current_setting('autovacuum_freeze_max_age')
FROM pg_database
ORDER BY 2 DESC;
datname |age | current_setting
++-
foo_db | 1022106099 | 2
template0 | 1000278345 | 2
postgres | 643729 | 2
template1 | 643729 | 2
(4 rows)
I've since allowed connections and ran "vacuumdb --freeze" on it and then
immediately disabled the connections to it again. But I'm curious how
template0 would be growing in age like this. Even now I see the template0
age growing. I can say that these DB has previously been altered for locale
changes as well.
I'm also running a long "vacuum freeze" on foo_db that will take a few days
after seeing that autovacuum on a big table had been running on it since
Feb 2 and making no progress, with over 850M dead tuples according to
pg_stat_all_tables. I estimate 3-4 more days to go on that one. Once that's
done I'll be scheduling manual vacuum jobs. Just wondering if that would
somehow affect regular template0 cleanup though.
I don't see anything in postgres log related to template0 other than my
manual interactions today.
--
Don Seiler
www.seiler.us
Re: Removing Last field from CSV string
On Sat, May 16, 2020 at 10:19 AM Alex Magnum wrote:
> Hi,
>
> I have a string that I want to cut to 60 char and then remove the last
> field and comma.
>
> substring('Class V,Class VI,Class VII,Competitive Exam,Class VIII,Class
> X,Class XI,Class IX,Class XII',1,60);
>
> substring | Class V,Class VI,Class VII,Competitive Exam,Class VIII*,Class*
>
> Now I try to remove the last field and comma ",Class"
>
> To get Class V,Class VI,Class VII,Competitive Exam,Class VIII
>
> Is there a function or easy way to do this?
> Any help would be appreciated.
>
> You should be able to write a posix patter that does this, you want to
> keep everything except a comma followed by 0 or more non-commas and the end
> of the string boundary to make sure it gets just the last such match.
>
--
Mike Nolan
no...@tssi.com
Re: template0 needing vacuum freeze?
Don Seiler writes: > PG 9.6 (9.6.8). Got an alert today when template0 and template1 were both > over 50% towards TXID wraparound. I could vacuum template1 but couldn't > vacuum template0 without first allowing connections. This is what it looked > like before: template0 shouldn't really need freezing, if it's unchanged since initdb, but the autovacuum logic doesn't know that and will periodically scan it anyway. That should be pretty cheap (since that DB is small and there's not really any work to do), so we haven't considered it to be something to prevent --- especially since it is a good safety valve in case someone does change template0. So it's unsurprising that the freeze age increases until autovacuum decides to do something about it. I'm suspicious that your alert settings are too aggressive and are notifying you before autovacuum kicks in. You should *not* have had to do anything manual about this, unless you have frobbed your autovac settings to the point of brokenness. regards, tom lane
Re: template0 needing vacuum freeze?
On Sat, May 16, 2020 at 12:44 PM Tom Lane wrote: > > So it's unsurprising that the freeze age increases until autovacuum > decides to do something about it. I'm suspicious that your alert settings are too aggressive and are notifying you before autovacuum kicks in. > You should *not* have had to do anything manual about this, unless you > have frobbed your autovac settings to the point of brokenness. > Shouldn't autovacuum have kicked in when the age of a table reaches 200M (our autovacuum_freeze_max_age is left at that default)? I see other tables in our app DB triggering the autovacuum "to prevent wrap-around" when they reach 200M. That's what had me concerned to see template0 with an age over 1B and no autovacuum even trying to clean up for it. Don. -- Don Seiler www.seiler.us
Using b-tree index for >= condition when joining
Hello All! I am having a problem with nudging postgres to choose a good plan for a query involving a left join and an inequality constraint on a column with b-tree index. Let's say both tbl1 and tbl2 tables have date column with an index on it. Queries like "SELECT * FROM tbl1 WHERE date >= CONSTANT" are using index scan, as expected. Now let's define a view: =# CREATE VIEW vw1 AS SELECT t1.date as date, t1.x as x, t2.y as y FROM tbl1 t1 LEFT JOIN tbl2 t2 USING (date); Query of the form "SELECT * FROM vw1 WHERE date = '2020-04-21'" is using index scan on both tables: =# EXPLAIN SELECT * FROM vw1 WHERE date = '2020-04-21'; QUERY PLAN Hash Right Join (cost=91208.02..112781024.50 rows=100 width=12) Hash Cond: (t2.date = t1.date) -> Index Scan using tbl2_date_idx on tbl2 t2 (cost=0.43..188393.92 rows=10 width=8) Index Cond: (date = '2019-04-21'::date) -> Hash (cost=89566.58..89566.58 rows=10 width=8) -> Bitmap Heap Scan on tbl1 t1 (cost=1875.43..89566.58 rows=10 width=8) Recheck Cond: (date = '2019-04-21'::date) -> Bitmap Index Scan on tbl1_date_idx (cost=0.00..1850.43 rows=10 width=0) Index Cond: (date = '2019-04-21'::date) (I know the total number of rows estimated for this and next queries is enormous, in reality there are more conditions on the join but I want to keep the example small) However when an inequality is used the query plan seems inefficient: =# EXPLAIN SELECT * FROM vw1 WHERE date >= '2020-04-21'; QUERY PLAN -- Hash Left Join (cost=483538.43..4617954384.38 rows=4104 width=12) Hash Cond: (t1.date = t2.date) -> Index Scan using tbl1_date_idx on tbl1 t1 (cost=0.43..369147.38 rows=4104000 width=8) Index Cond: (date >= '2019-04-21'::date) -> Hash (cost=234163.00..234163.00 rows=1520 width=8) -> Seq Scan on tbl2 t2 (cost=0.00..234163.00 rows=1520 width=8) It looks like the inequality on date isn't pushed down below the left join? I can get the plan I'd like to have by putting the same constraint on the date column on the second table: =# EXPLAIN SELECT * FROM tbl1 t1 LEFT JOIN tbl2 t2 USING (date) WHERE t1.date >= '2019-04-21' AND t2.date >= '2019-04-21'; QUERY PLAN -- Hash Join (cost=281625.87..1651822721.88 rows=11286000 width=26) Hash Cond: (t2.date = t1.date) -> Index Scan using tbl2_date_idx on tbl2 t2 (cost=0.43..369784.44 rows=418 width=15) Index Cond: (date >= '2019-04-21'::date) -> Hash (cost=210285.43..210285.43 rows=4104000 width=15) -> Bitmap Heap Scan on tbl1 t1 (cost=76822.43..210285.43 rows=4104000 width=15) Recheck Cond: (date >= '2019-04-21'::date) -> Bitmap Index Scan on tbl1_date_idx (cost=0.00..75796.43 rows=4104000 width=0) Index Cond: (date >= '2019-04-21'::date) Is it possible to define a view vw2 such that queries of the form "SELECT * FROM vw2 WHERE date >= CONSTANT" use the plan I pasted above? Thanks in advance for help, Lukasz
Re: Removing Last field from CSV string
On 16.05.20 17:18, Alex Magnum wrote: > Now I try to remove the last field and comma ",Class" > > To get Class V,Class VI,Class VII,Competitive Exam,Class VIII > > Is there a function or easy way to do this? > Any help would be appreciated. > Hi Alex Many options to do this with regexp_replace, here's one way: with test as ( select 'Class VII,Competitive Exam,Class VIII,Class' as str union select 'Class VIIx,Competitive Exam22,Class VIIIabc,Classx' ) select str, regexp_replace(str, '^(.*),(.*?)$', '\1') res from test; |str |res | |--| |Class VII,Competitive Exam,Class VIII,Class |Class VII,Competitive Exam,Class VIII |--| |Class VIIx,Competitive Exam22,Class VIIIabc,Classx |Class VIIx,Competitive Exam22,Class VIIIabc | (I cut some columns at the start to better fit email width) Cheers Christian -- Christian Ramseyer, netnea ag Network Management. Security. OpenSource. https://www.netnea.com
Re: Inherited an 18TB DB & need to backup
On 5/16/20 7:18 AM, Rob Sargent wrote: O Another problem is storage devices fail. S3 storage lakes _should_ be checking your data integrity on a regular basis and possibly maintaining copies of it iin multiple locations so you're not vulnerable to a site disaster. Tape FTW!! Or WTF Tape?? :) Tape is durable, long-lasting, high-density, under your control, can be taken off-site (don't underestimate the bandwidth of a station wagon full of tapes hurtling down the highway!) and -- with the proper software -- is multi-threaded. -- Angular momentum makes the world go 'round.
Re: Inherited an 18TB DB & need to backup
On 17/05/2020 08:12, Ron wrote: On 5/16/20 7:18 AM, Rob Sargent wrote: O Another problem is storage devices fail. S3 storage lakes _should_ be checking your data integrity on a regular basis and possibly maintaining copies of it iin multiple locations so you're not vulnerable to a site disaster. Tape FTW!! Or WTF Tape?? :) Tape is durable, long-lasting, high-density, under your control, can be taken off-site (don't underestimate the bandwidth of a station wagon full of tapes hurtling down the highway!) and -- with the proper software -- is multi-threaded. Don't you mean multi-spooled??? :-) Fascinating problem. If the dump & load programs are designed to take a parameter for N drives for effective parallel operation, and N > 2, then things will run a lot faster. I can think of several ways the the data can be dumped in parallel, with various trade-offs. Would love to know how it's implemented in practice. Cheers, Gavn
Ubuntu 20.04: apt.postgresql.org/pub/repos/apt focal-pgdg InRelease' doesn't support architecture 'i386'
Hi, While this doesn't appear to be a bug that causes problems of any kind, I do have a question about its cause. The "error" listed in the Subject: line is basically what I'm seeing. The entire message is below, particularly the 'N:' at the end. Is there a repo setting I should change to prevent the request for '386' architecture? Thank you in advance for your assistance. user@ubuntu:~$ sudo apt update Hit:1 http://us.archive.ubuntu.com/ubuntu focal InRelease Get:2 http://us.archive.ubuntu.com/ubuntu focal-updates InRelease [107 kB] Get:3 http://security.ubuntu.com/ubuntu focal-security InRelease [107 kB] Get:4 http://us.archive.ubuntu.com/ubuntu focal-backports InRelease [98.3 kB] Get:5 http://us.archive.ubuntu.com/ubuntu focal-updates/main amd64 DEP-11 Metadata [90.4 kB] Hit:6 http://apt.postgresql.org/pub/repos/apt focal-pgdg InRelease Get:7 http://us.archive.ubuntu.com/ubuntu focal-updates/universe amd64 DEP-11 Metadata [21.4 kB] Get:8 http://us.archive.ubuntu.com/ubuntu focal-backports/universe amd64 DEP-11 Metadata [532 B] Get:9 http://security.ubuntu.com/ubuntu focal-security/main amd64 DEP-11 Metadata [16.6 kB] Get:10 http://security.ubuntu.com/ubuntu focal-security/universe amd64 DEP-11 Metadata [208 B] Fetched 441 kB in 1s (367 kB/s) Reading package lists... Done Building dependency tree Reading state information... Done All packages are up to date. N: Skipping acquire of configured file 'main/binary-i386/Packages' as repository 'http://apt.postgresql.org/pub/repos/apt focal-pgdg InRelease' doesn't support architecture 'i386' Thanks, Hugh
Re: Ubuntu 20.04: apt.postgresql.org/pub/repos/apt focal-pgdg InRelease' doesn't support architecture 'i386'
On 5/16/20 3:45 PM, Hugh wrote: Hi, While this doesn't appear to be a bug that causes problems of any kind, I do have a question about its cause. The "error" listed in the Subject: line is basically what I'm seeing. The entire message is below, particularly the 'N:' at the end. Is there a repo setting I should change to prevent the request for '386' architecture? Thank you in advance for your assistance. user@ubuntu:~$ sudo apt update Hit:1 http://us.archive.ubuntu.com/ubuntu focal InRelease Get:2 http://us.archive.ubuntu.com/ubuntu focal-updates InRelease [107 kB] Get:3 http://security.ubuntu.com/ubuntu focal-security InRelease [107 kB] Get:4 http://us.archive.ubuntu.com/ubuntu focal-backports InRelease [98.3 kB] Get:5 http://us.archive.ubuntu.com/ubuntu focal-updates/main amd64 DEP-11 Metadata [90.4 kB] Hit:6 http://apt.postgresql.org/pub/repos/apt focal-pgdg InRelease Per here: https://www.postgresql.org/download/linux/ubuntu/ shouldn't the above be: focal-pgdg main Get:7 http://us.archive.ubuntu.com/ubuntu focal-updates/universe amd64 DEP-11 Metadata [21.4 kB] Get:8 http://us.archive.ubuntu.com/ubuntu focal-backports/universe amd64 DEP-11 Metadata [532 B] Get:9 http://security.ubuntu.com/ubuntu focal-security/main amd64 DEP-11 Metadata [16.6 kB] Get:10 http://security.ubuntu.com/ubuntu focal-security/universe amd64 DEP-11 Metadata [208 B] Fetched 441 kB in 1s (367 kB/s) Reading package lists... Done Building dependency tree Reading state information... Done All packages are up to date. N: Skipping acquire of configured file 'main/binary-i386/Packages' as repository 'http://apt.postgresql.org/pub/repos/apt focal-pgdg InRelease' doesn't support architecture 'i386' Thanks, Hugh -- Adrian Klaver adrian.kla...@aklaver.com
Re: Ubuntu 20.04: apt.postgresql.org/pub/repos/apt focal-pgdg InRelease' doesn't support architecture 'i386'
On Sun, May 17, 2020 at 10:45 AM Hugh wrote: > While this doesn't appear to be a bug that causes problems of any kind, I do > have a question about its cause. > > The "error" listed in the Subject: line is basically what I'm seeing. The > entire message is below, particularly the 'N:' at the end. Is there a repo > setting I should change to prevent the request for '386' architecture? Thank > you in advance for your assistance. I'm not sure, but it seems related to this complaint and the answer might be to tell your sources.list that that source has only amd64: https://www.postgresql.org/message-id/flat/16402-1f2d77e819f9e1f2%40postgresql.org
Re: Ubuntu 20.04: apt.postgresql.org/pub/repos/apt focal-pgdg InRelease' doesn't support architecture 'i386'
Hugh writes: > Hi, > > While this doesn't appear to be a bug that causes problems of any kind, I do > have a question about its cause. > > The "error" listed in the Subject: line is basically what I'm seeing. The > entire message is below, particularly the 'N:' at the end. Is there a repo > setting I should change to prevent the request for '386' architecture? Thank > you in advance for your assistance. > > user@ubuntu:~$ sudo apt update > > Hit:1 http://us.archive.ubuntu.com/ubuntu focal InRelease > Get:2 http://us.archive.ubuntu.com/ubuntu focal-updates InRelease [107 kB] > > Get:3 http://security.ubuntu.com/ubuntu focal-security InRelease [107 kB] > > Get:4 http://us.archive.ubuntu.com/ubuntu focal-backports InRelease [98.3 kB] > > Get:5 http://us.archive.ubuntu.com/ubuntu focal-updates/main amd64 DEP-11 > Metadata [90.4 kB] > Hit:6 http://apt.postgresql.org/pub/repos/apt focal-pgdg InRelease > > Get:7 http://us.archive.ubuntu.com/ubuntu focal-updates/universe amd64 DEP-11 > Metadata [21.4 kB] > Get:8 http://us.archive.ubuntu.com/ubuntu focal-backports/universe amd64 > DEP-11 Metadata [532 B] > Get:9 http://security.ubuntu.com/ubuntu focal-security/main amd64 DEP-11 > Metadata [16.6 kB] > Get:10 http://security.ubuntu.com/ubuntu focal-security/universe amd64 DEP-11 > Metadata [208 B] > Fetched 441 kB in 1s (367 kB/s) > Reading package lists... Done > Building dependency tree > Reading state information... Done > All packages are up to date. > N: Skipping acquire of configured file 'main/binary-i386/Packages' as > repository 'http://apt.postgresql.org/pub/repos/apt focal-pgdg InRelease' > doesn't support architecture 'i386' > This error is because by default the debian package manager is trying to download details on all supported architectures, but failing to find one for i386. You can add an architecture tag to the repository definition in the source list file i.e. /etc/apt/sources.list or /etc/apt/sources.list.d/postgres.list (or whatever you have called itIf). Try adding the arch option as deb [ arch=amd64 ] http://. deb-src [arch=amd64 ] ... This should tell apt to only look for the amd64 packages. -- Tim Cross
Re: Using b-tree index for >= condition when joining
=?UTF-8?B?xYF1a2FzeiBExIViZWs=?= writes: > I am having a problem with nudging postgres to choose a good plan for > a query involving a left join and an inequality constraint on a column > with b-tree index. > ... > It looks like the inequality on date isn't pushed down below the left > join? Nope. The planner only derives implied conditions from equality clauses. There've been discussions about that in the past, but it was (and remains) unclear that trying to account for other clause types would be a net win. The planner-cycles-expended versus number-of-queries-improved tradeoff doesn't look promising. > I can get the plan I'd like to have by putting the same > constraint on the date column on the second table: Note that you're not really getting the same plan that way: it's not a left join anymore, because you put a strict constraint on the join's inner relation, so the planner realizes it doesn't have to produce any null-extended rows. You could make it work with the desired semantics with something along the lines of SELECT * FROM tbl1 t1 LEFT JOIN (select * from tbl2 where tbl2.date >= '2019-04-21') t2 USING (date) WHERE t1.date >= '2019-04-21'; but of course that's even less easy :-( regards, tom lane
Re: Inherited an 18TB DB & need to backup
On 5/16/20 3:30 PM, Gavin Flower wrote: On 17/05/2020 08:12, Ron wrote: On 5/16/20 7:18 AM, Rob Sargent wrote: O Another problem is storage devices fail. S3 storage lakes _should_ be checking your data integrity on a regular basis and possibly maintaining copies of it iin multiple locations so you're not vulnerable to a site disaster. Tape FTW!! Or WTF Tape?? :) Tape is durable, long-lasting, high-density, under your control, can be taken off-site (don't underestimate the bandwidth of a station wagon full of tapes hurtling down the highway!) and -- with the proper software -- is multi-threaded. Don't you mean multi-spooled??? :-) That's a superset of multi-threaded IO. Fascinating problem. If the dump & load programs are designed to take a parameter for N drives for effective parallel operation, and N > 2, then things will run a lot faster. I can think of several ways the the data can be dumped in parallel, with various trade-offs. Would love to know how it's implemented in practice. An OS with asynchronous, queued, non-blocking IO, and a programming language with callbacks. OpenVMS has had it since since *at least* the early 1990s, and probably mid-1980s. I remember backing up an Rdb/VMS database to 10 tape drives at the same time. Typically, though, we "only" used six tape drives for that database, because we simultaneously backed up multiple databases. -- Angular momentum makes the world go 'round.
