Explain returns different number of rows

[Vince McMahon]

I did get reply so I am trying again.

I executed the following statements 3 times
>  explain(analyze, buffet) select * from table1
>
> The number of rows are different.  Is the table corrupted?  How to confirm
> and how to fix it?
>

Does standalone postfrsql have autogrowth/ manual growth on table space?

[Vince McMahon]

In other databases, there is a way to preallocate the table space to allow
bulk loading of data in a well packed and continuous space.


Does psql have that auto/manual growth?

Re: Does standalone postfrsql have autogrowth/ manual growth on table space?

[Vince McMahon]

Thanks.  I guess it is time for vacuum. 😂

On Thu, Oct 20, 2022, 13:34 Christophe Pettus  wrote:

>
>
> > On Oct 20, 2022, at 10:29, Ron  wrote:
> > There's no pre-allocation in Postgresql.
>
> This is correct in that you can't say "please allocate 3GB for this table
> for me now."  The number of pages that are added to the end of a file isn't
> always one though; it can allocate more based on recent activity.
>
>

Re: Explain returns different number of rows

[Vince McMahon]

Thanks for the clarification, Peter.



On Sat, Oct 22, 2022, 05:32 Peter J. Holzer  wrote:

> On 2022-10-20 09:56:23 -0700, Christophe Pettus wrote:
> > On Oct 20, 2022, at 09:52, Vince McMahon 
> wrote:
> > > The number of rows are different.
> >
> > This isn't unexpected.  EXPLAIN does not actually run the query and
> > determine how many rows are returned; it calculates an estimate based
> > on the current system statistics, which vary constantly depending on
> > activity in the database.
>
> EXPLAIN ANALYZE (which is what he did) does run the query and return the
> actual number of rows:
>
> #v+
> wdsah=> explain (analyze, buffers) select * from
> facttable_eurostat_comext_cpa2_1 ;
>
> ╔══╗
> ║QUERY
> PLAN║
>
> ╟──╢
> ║ Seq Scan on facttable_eurostat_comext_cpa2_1  (cost=0.00..1005741.32
> rows=39633432 width=85) (actual time=0.396..6541.701 rows=39633591 loops=1)
> ║
> ║   Buffers: shared read=609407
> ║
> ║ Planning Time: 1.650 ms
> ║
> ║ Execution Time: 7913.027 ms
> ║
>
> ╚══╝
> (4 rows)
> #v-
>
> The first tuple (cost=0.00..1005741.32 rows=39633432 width=85) is an
> estimate used to plan the query. But the second one
> (actual time=0.396..6541.701 rows=39633591 loops=1)
> contains measurements from actually running the query.
>
> I think it's possible that the rows estimate in the first tuple changes
> without any actual data change (although the only reason I can think of
> right now would be an ANALYZE (in another session or by autovacuum)).
> But the actual rows definitely shouldn't change.
>
> hp
>
> --
>_  | Peter J. Holzer| Story must make more sense than reality.
> |_|_) ||
> | |   | h...@hjp.at |-- Charles Stross, "Creative writing
> __/   | http://www.hjp.at/ |   challenge!"
>

Is there psql cluster or replication

[Vince McMahon]

Is there cluster on postgres?  Or, there is replication, not cluster.

If there is active/passive cluster or active/active , may I have the steps
to configure them?

Impact from removal of pgadmin4

[Vince McMahon]

In windows, when the pgadmin4 is removed, will it remove the libraries from
that windows?

Basically, I have other database applications installed to get and to set
data into postgres db.  Will removing of pgadmin4 remove the odbc, jdbc
drivers these application use?

Thanks.

Re: Impact from removal of pgadmin4

[Vince McMahon]

Thanks for the deep insight.  You are better than OpenChat gpt 4.

Happy Friday.

On Fri, Aug 2, 2024, 8:53 AM Ron Johnson  wrote:

> On Fri, Aug 2, 2024 at 3:57 AM Vince McMahon <
> sippingonesandze...@gmail.com> wrote:
>
>> In windows, when the pgadmin4 is removed, will it remove the libraries
>> from that windows?
>>
>> Basically, I have other database applications installed to get and to set
>> data into postgres db.  Will removing of pgadmin4 remove the odbc, jdbc
>> drivers these application use?
>>
>
> JDBC is a separately-installed  package (which makes me think that you're
> running a very old version of the JDBC driver), and I'm betting that the
> ODBC driver is also separately-installed.
>
>

How to validate restore of backup?

[Vince McMahon]

Hi,

I have some questions When doing pg_restore of backup of a database to a
NEW server.

Is there a way to ensure the data integrity is in tact, and user ID and
access works liked how it was in the old server?

How to properly handle the materialized views when backing up and restoring?

Thanks.

Re: How to validate restore of backup?

[Vince McMahon]

Hi, Adrian.

The largest one is 8 GB after compression.

I have a window of 8 hours to handle 30 GB total of backup at various sizes.



On Thu, Aug 22, 2024, 11:36 AM Adrian Klaver 
wrote:

> On 8/22/24 04:06, Vince McMahon wrote:
> > Hi,
> >
> > I have some questions When doing pg_restore of backup of a database to a
> > NEW server.
>
> How large a backup?
>
> >
> > Is there a way to ensure the data integrity is in tact, and user ID and
> > access works liked how it was in the old server?
>
> As to user access, write tests that cover that and run on the new cluster.
>
> Data is trickier and if that is possible to a degree of certainty is
> going to depend on answer to the first question above.
>
> >
> > How to properly handle the materialized views when backing up and
> restoring?
>
> create materialized view prj_mv(p_item_no, year) as select p_item_no,
> year from projection with data;
>
> pg_dump -d production -U postgres -h localhost -t projection -t prj_mv
> -f prj.sql
>
> In prj.sql:
>
> CREATE MATERIALIZED VIEW public.prj_mv AS
>   SELECT p_item_no,
>  year
> FROM public.projection
>WITH NO DATA;
>
> COPY public.projection ( ...
>
>
> [...]
>
> REFRESH MATERIALIZED VIEW public.prj_mv;
>
> It is done for you.
>
> >
> > Thanks.
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>