Re: Visibility of data from table inherits function
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 documentation. > So far it seems that functions 'joins' may be used to view data, whilst the > functions 'views' is unable to be used with data entry. > Please, any suggestions of an alternative function to use? I would first suggest you try to describe what you are trying to achieve, just to avoid https://xyproblem.info. If what you want to achieve is copying what is in table PARENT when you create table CHILD the solution is easy, providing all the extra columns have DEFAULT values, just use a CREATE LIKE followed by an INSERT INTO CHILD SELECT * FROM PARENT, it is a simple two liner that you can wrap in a function if you need to use it a lot in your work flow. If you want to see the rows of BOTH when you select from CHILD you need a VIEW, as PARENT has missing columns. As you have not stated what you want to achieve, even in the examples, not much can be suggested, hence the cite of XYproblem. Francisco Olarte.
VACUUM FULL missing chunk number 0 for toast value
Hi All, OS: CentOS 7.6 PG: 11.11 Once we tried to vacuum full a table, got the error msg "ERROR: missing chunk number 0 for toast value", there is a doc as below for the select issue, but for our case select is no issue, what's the reason caused and how to fix this please? Thanks https://newbiedba.wordpress.com/2015/07/07/postgresql-missing-chunk-0-for-toast-value-in-pg_toast/ 1. vacuum full output: VACUUM FULL VERBOSE ANALYZE application.notes; INFO: vacuuming "application.notes" ERROR: missing chunk number 0 for toast value 183500290 in pg_toast_16977 2. explain select output: explain select * from application.notes; QUERY PLAN -- Seq Scan on notes (cost=0.00..430924.43 rows=412443 width=767) Thanks and regards Sun Yi
Re: pg_config header files are missing - Postgres 13 - Amazon Linux 2
This problem posted in OP is resolved. I did some workaround. I copied the
required "pg_config*.h" from another machine where PG13 was installed via
amazon linux 2 repos.
> I don't believe 9.6 was supported either, but it may have worked "by
chance".
I'm not sure whether it worked "by chance" or otherwise, because it looks
like Amazon Linux 2 is based on "RHEL 7" as per below:
Note the "ID_LIKE":
# cat /etc/*elease
NAME="Amazon Linux"
VERSION="2"
ID="amzn"
ID_LIKE="centos rhel fedora"
VERSION_ID="2"
PRETTY_NAME="Amazon Linux 2"
ANSI_COLOR="0;33"
CPE_NAME="cpe:2.3:o:amazon:amazon_linux:2"
HOME_URL="https://amazonlinux.com/";
Amazon Linux release 2 (Karoo)
And:
---
# rpm -E %{rhel}
7
Thanks!
On Sun, 2 Jan 2022 at 00:58, Magnus Hagander wrote:
> On Fri, Dec 31, 2021 at 3:32 AM Hasan Marzooq
> wrote:
> >
> > Hi.
> > I tried "yum install centos-release-scl-rh" before but it gives the
> below error.
> > --
> > # yum install centos-release-scl-rh
> > Loaded plugins: extras_suggestions, langpacks, priorities, update-motd
> > 213 packages excluded due to repository priority protections
> > No package centos-release-scl-rh available.
> > Error: Nothing to do
> >
> >
> > Postgres 9.6 on this machine (Amazon Linux 2) is installed via PGDG
> repo, so I tried installing Postgres 13 as well, and I can see it has
> installed but only one more package I need that is
> "postgresql13-devel.x86_64" which needs another dependency "
> llvm-toolset-7-clang >= 4.0.1" that is not available from the same repo.
>
> I don't believe 9.6 was supported either, but it may have worked "by
> chance".
>
> The llvm dependency comes from the JIT functionality, which was added
> in PostgreSQL 11, so it not being a dependency in an older version
> than that makes perfect sense.
>
> --
> Magnus Hagander
> Me: https://www.hagander.net/
> Work: https://www.redpill-linpro.com/
>
Re: VACUUM FULL missing chunk number 0 for toast value
Yi Sun writes: > Once we tried to vacuum full a table, got the error msg "ERROR: missing > chunk number 0 for toast value", there is a doc as below for the select > issue, but for our case select is no issue, Hmm, "SELECT *" doesn't throw any error? That suggests that the missing-toast-data problem is in a row that SELECT ignores because it's dead, but VACUUM FULL is trying to copy it anyway, which would imply that VACUUM thinks it's still visible to some old transaction. Check to see if you've got old open transactions (particularly prepared-and-never-committed transactions). That's just a workaround of course. It's not very clear whether there's any actual data corruption here, but if you're not having SELECT problems, it seems like whatever it is isn't critical. regards, tom lane
postgres_fdw FAST "where id in (140,144,148)" SLOW: "where id in (select 140 as id union select 144 union select 148)"
Hi I have postgres_fdw table called tbl_link. The source table is 2.5 GB in size with 122 lines (some lines has 70MB bytea column, but not the ones I select in the example) I noticed that when I put the specific ids in the list "where id in (140,144,148)" it works fast (few ms), but when I put the same list as select "where id in (select 140 as id union select 144 union select 148)" it takes 50 seconds. This select union is just for the example, I obviously have a different select (which by itself takes few ms but cause the whole insert query to take 1x more time) Why is that? How can I still use regular select and still get reasonable response time? Thanks FAST: select lnk.* into local_1 from tbl_link lnk where id in (140,144,148) "Foreign Scan on tbl_link lnk (cost=100.00..111.61 rows=3 width=700) (actual time=4.161..4.167 rows=3 loops=1)" "Planning Time: 0.213 ms" "Execution Time: 16.251 ms" SLOW: select lnk.* into local_1 from tbl_link lnk where id in (select 140 as id union select 144 union select 148) "Hash Join (cost=100.18..113.88 rows=3 width=700) (actual time=45398.721..46812.100 rows=3 loops=1)" " Hash Cond: (lnk.id = (140))" " -> Foreign Scan on tbl_link lnk (cost=100.00..113.39 rows=113 width=700) (actual time=45398.680..46812.026 rows=112 loops=1)" " -> Hash (cost=0.14..0.14 rows=3 width=4) (actual time=0.023..0.026 rows=3 loops=1)" "Buckets: 1024 Batches: 1 Memory Usage: 9kB" "-> HashAggregate (cost=0.08..0.11 rows=3 width=4) (actual time=0.017..0.021 rows=3 loops=1)" " Group Key: (140)" " Batches: 1 Memory Usage: 24kB" " -> Append (cost=0.00..0.07 rows=3 width=4) (actual time=0.005..0.009 rows=3 loops=1)" "-> Result (cost=0.00..0.01 rows=1 width=4) (actual time=0.003..0.004 rows=1 loops=1)" "-> Result (cost=0.00..0.01 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)" "-> Result (cost=0.00..0.01 rows=1 width=4) (actual time=0.000..0.001 rows=1 loops=1)" "Planning Time: 0.541 ms" "Execution Time: 46827.945 ms" FAST IMPORTANT - This email and any attachments is intended for the above named addressee(s), and may contain information which is confidential or privileged. If you are not the intended recipient, please inform the sender immediately and delete this email: you should not copy or use this e-mail for any purpose nor disclose its contents to any person.
