> On Oct 9, 2024, at 5:52 AM, Torsten Förtsch wrote:
>
> Filenames like 16665, 16665.1, 16665.2 etc all represent the same table (or
> similar). The number 16665 is called the file node.
>
> To get a list of file nodes for a specific database you can run:
>
> SELECT oid::regclass::text, rel
> On Sep 26, 2024, at 7:03 AM, yudhi s wrote:
>
> In a RDS postgres ...
> Is it fine to let it use "FreeLocalStorage" unless it goes till zero?
Hi Yudhi,
FreeLocalStorage and some of the other things you ask about are specific to AWS
RDS, so you might have better luck getting answers on an
> On Jan 16, 2024, at 4:19 PM, David G. Johnston
> wrote:
>
> On Tuesday, January 16, 2024, Atul Kumar wrote:
> Hi,
>
> I am new to RDS postgres, I have version 14 running on it with m7g.large
>
> I found that lots of parameters has DBInstanceClassMemory written, so what
> exactly is the
> On Nov 3, 2023, at 9:45 AM, Bruce Momjian wrote:
>
> On Fri, Nov 3, 2023 at 09:39:46AM -0400, Philip Semanchuk wrote:
>>
>> In addition to Bruce Momjian’s suggestion, I’ll add that you can make an
>> HTML version of the manual from the source code. This is
> On Nov 3, 2023, at 9:18 AM, Ben Hancock wrote:
>
> Hi all:
>
> Does Postgres come with a local, full version of the manual installed
> by default anywhere (i.e. akin to what is available on the website, but
> in man, info, or plain-text format)? When I invoke `man postgres`, I do
> get a ve
> On Mar 13, 2023, at 5:38 PM, Celia McInnis wrote:
>
> HI:
>
> I would be really happy if postgresql had an upper case version of the ß
> german character. The wiki page
> https://en.wikipedia.org/wiki/%C3%9F
>
> indicates that the capital (U+1E9E ẞ LATIN CAPITAL LETTER SHARP S) was
> e
> On Feb 7, 2023, at 3:30 AM, Laurenz Albe wrote:
>
> On Mon, 2023-02-06 at 12:04 -0500, Philip Semanchuk wrote:
>> I have a column defined GENERATED ALWAYS AS {my_expression} STORED. I’d like
>> to change the
>> {my_expression} part. After reading the documentatio
Hi all,
I have a column defined GENERATED ALWAYS AS {my_expression} STORED. I’d like to
change the {my_expression} part. After reading the documentation for ALTER
TABLE (https://www.postgresql.org/docs/current/sql-altertable.html) and trying
a few things that resulted in syntax errors, there doe
> On Nov 10, 2022, at 3:39 PM, Tom Lane wrote:
>
> Joe Conway writes:
>>
>> CREATE OR REPLACE FUNCTION mood2text(mood)
>> RETURNS text AS
>> $$
>> select $1
>> $$ STRICT IMMUTABLE LANGUAGE sql;
>
> Of course, what this is doing is using a SQL-function wrapper to
> lie about the mutability
Hi all,
I know that Postgres' enum_in()/enum_out() functions have a volatility class of
STABLE, and STABLE is required because enum elements can be renamed. We have an
enum in our database used in a number of custom functions, all of which require
casting the enum to/from text. Since enum_in() a
Hi,
I have a custom function where the ROWS hint is getting ignored. I think it’s
because the function is getting inlined, but I’d like a second opinion.
Here’s my working (contrived) example.
CREATE TABLE my_table (
id int primary key GENERATED ALWAYS AS IDENTITY,
base_value int NOT NUL
> On Jun 3, 2022, at 4:19 AM, Andreas Joseph Krogh wrote:
>
> Hi, I have set join_collapse_limit = 12 in production, but I'm thinking about
> raising it to 16.
> On modern HW is there a “sane maximum” for this value?
> I can easily spare 10ms for extra planning per query on our workload, is 1
Hi all,
I recently discovered that a custom function that I thought was being inlined
was not being inlined because I had declared it IMMUTABLE, but the function
body cast an enum value to text which is a STABLE operator. Once I corrected my
function's definition to declare it STABLE, Postgres i
> On Apr 20, 2022, at 3:18 PM, Guyren Howe wrote:
>
> I’ve really only ever worked in web development. 90+% of web developers
> regard doing anything at all clever in the database with suspicion.
>
> I’m considering working on a book about implementing business logic in
> Postgres, and I’m
> On Mar 29, 2022, at 2:24 PM, Tom Lane wrote:
>
> Philip Semanchuk writes:
>> I have a function that isn't being inlined, and I would appreciate help to
>> understand why that's the case.
>
> I think the test methodology you used is faulty, beca
Hi all,
I have a function that isn't being inlined, and I would appreciate help to
understand why that's the case.
I'm using PG 11.15. I know that if I declare my function IMMUTABLE and it calls
a non-IMMUTABLE function, Postgres won't inline my function. But even when my
function calls only s
should affect all of our
functions equally, not just the ones that start with comments.
I clearly don’t understand this problem fully. Although I'm curious about it,
I’m eager to move on to other things. I plan to proceed with this fix and not
investigate any more.
THanks everyone fo
> On Mar 25, 2022, at 11:59 AM, Tom Lane wrote:
>
> Philip Semanchuk writes:
>> I'm trying to understand a behavior where, with our Postgres client, a
>> leading comment in a SQL script causes the CREATE FUNCTION statement
>> following it to be not executed.
Hi,
I'm trying to understand a behavior where, with our Postgres client, a leading
comment in a SQL script causes the CREATE FUNCTION statement following it to be
not executed. I can't figure out if this is a bug somewhere or just a
misunderstanding on my part. I would appreciate some help under
> On Oct 29, 2021, at 2:05 PM, Tom Lane wrote:
>
> "David G. Johnston" writes:
>> On Friday, October 29, 2021, Philip Semanchuk
>> wrote:
>>> I would appreciate help with the syntax for querying an array of strings
>>> declared as
Hi,
I would appreciate help with the syntax for querying an array of strings
declared as a psql variable. Here's an example.
\set important_days ARRAY['monday', 'friday']
select 1 where 'monday' = ANY(:important_days);
ERROR: 42703: column "monday" does not exist
LINE 1: select 1 where 'monday'
> On Aug 17, 2021, at 12:21 PM, Michael White wrote:
>
> This is my first post so if I’m in the wrong place please help me get to the
> correct list.
>
> Issue:
>
> I installed PostgreSQL on my Mac over a year ago and since upgraded my disk
> drive and OS to Big Sur. I am a retired Softwa
> On Jul 29, 2021, at 3:09 AM, Lucas wrote:
>
> Hello,
>
> I have recently deployed a new Slave (streaming replication) and have been
> monitoring its cache hit ratio.
>
> At the moment, read-only queries are being sent to this slave but only 10% of
> the traffic.
> The cache hit ratio is
> On Jun 7, 2021, at 11:07 AM, Rob Sargent wrote:
>
> On 6/7/21 9:02 AM, Rich Shepard wrote:
>> This is an unusual message and I hope someone(s) here can offer insights
>> into the cause of the problem I've been wrestling with for the past couple
>> of weeks.
>>
>> Context: For the past decad
> On Jun 1, 2021, at 3:23 PM, Thomas Munro wrote:
>
> On Wed, Jun 2, 2021 at 7:15 AM Vijaykumar Jain
> wrote:
>> i only get workers to create mv, but refresh mv plan does not use workers
>> for the same conf params.
>
> Yeah, this changed in v14:
>
> https://git.postgresql.org/gitweb/?p=po
-+
| QUERY PLAN|
|---|
| Utility statements have no plan structure |
+---+
Cheers
Philip
>
> On Tue, 1 Jun 2021 at 23:30, Philip Semanchuk
> wrote:
> Hi all,
> Should I expect a planner difference betwe
Hi all,
Should I expect a planner difference between CREATE MATERIALIZED VIEW and
REFRESH MATERIALIZED VIEW? We have a materialized view that uses 4 workers
during CREATE but only one worker during REFRESH, and as a result the refresh
takes much longer (~90 minutes vs. 30 minutes for the CREATE)
> On May 26, 2021, at 10:04 PM, Rob Sargent wrote:
>
>
>
>> On May 26, 2021, at 4:37 PM, Ian Harding wrote:
>>
>>
>> There is an option to send the logs to cloudwatch which makes it less awful
>> to look at them.
> I have that but precious little of interest there. Lots of autovac, a
>
> On Feb 15, 2021, at 3:55 PM, Tom Lane wrote:
>
> Philip Semanchuk writes:
>> I saw some unexpected behavior that I'm trying to understand. I suspect it
>> might be a quirk specific to AWS Aurora and I'd like to confirm that.
>
>> When I restart my
Hi all,
I saw some unexpected behavior that I'm trying to understand. I suspect it
might be a quirk specific to AWS Aurora and I'd like to confirm that.
When I restart my local Postgres instance (on my Mac), the values in
pg_stat_user_tables.n_mod_since_analyze are preserved. In other words, if
> On Feb 9, 2021, at 10:52 AM, Thorsten Schöning wrote:
>
> So, based on which facts does Postgres decide if to use aadditional
> workers or not? Can I see those decisions explained somewhere? I don't
> see anything in the query plan. Thanks!
Hi Thorsten,
This is an interesting topic for me t
> On Jan 19, 2021, at 6:33 AM, Jayadevan M wrote:
>
>
>
> So I’m looking for a way to identify if the refresh process is finished or if
> it’s still running - preferably without having to wait for timeout by
> querying a locked materialized view. But by e.g. using the system tables or
>
> On Dec 31, 2020, at 7:20 AM, Joao Miguel Ferreira
> wrote:
>
> Hello,
>
> I'm a fan of unit testing and related matters but have used it only on client
> applications, not on database implemented logic. I recently joined a project
> whit dozens of PL functions and procedures.
>
> So, it
> On Dec 22, 2020, at 8:40 AM, Laurenz Albe wrote:
>
> On Mon, 2020-12-21 at 11:45 -0500, Philip Semanchuk wrote:
>>> On Dec 19, 2020, at 12:59 AM, Joel Jacobson wrote:
>>> Is there a way to avoid excessive inlining when writing pure SQL functions,
>>
> On Dec 19, 2020, at 12:59 AM, Joel Jacobson wrote:
>
> Is there a way to avoid excessive inlining when writing pure SQL functions,
> without having to use PL/pgSQL?
Hi Joel,
The rules for inlining are here:
https://wiki.postgresql.org/wiki/Inlining_of_SQL_functions
According to those rule
> On Nov 19, 2020, at 3:05 AM, Atul Kumar wrote:
>
> Hi,
>
> I have below queries:
>
> 1. How do i check the maintenance_work_mem for current session, before
> setting some other value for this parameter for the same session.
>
> 2. and How do I set maintenance_work_mem for a session only,
> On Nov 5, 2020, at 8:49 AM, Vasu Madhineni wrote:
>
> Hi All,
>
> In my organisation a newly built project application team requirement on
> tables like have a column (text type), with size can reach around 3 MB, and
> 45 million records annually.
>
> Are there any specific precautions/
ocess that writes to a different mat view (tmp_throwaway_mat_view)
without the CONCURRENTLY keyword and see if it behaves similarly.
>
> On Mon, Oct 26, 2020, 9:21 AM Philip Semanchuk
> wrote:
>
>
> > On Oct 25, 2020, at 10:52 PM, Ayub M wrote:
> >
> > T
e base
> table (which is 2 mins).
Do you know if it’s executing a different plan when it takes a long time?
auto_explain can help with that.
>
> On Fri, Oct 23, 2020 at 10:53 AM Philip Semanchuk
> wrote:
>
>
> > On Oct 23, 2020, at 9:52 AM, Ravi Krishna wrote:
>
> On Oct 23, 2020, at 9:52 AM, Ravi Krishna wrote:
>
>> My understanding is that when CONCURRENTLY is specified, Postgres implements
>> the refresh as a series of INSERT, UPDATE,
>> and DELETE statements on the existing view. So the answer to your question
>> is no, Postgres doesn’t create a
> On Oct 22, 2020, at 3:53 PM, Ayub M wrote:
>
> There is a table t which is used in a mview mv, this is the only table in the
> mview definition.
>
> create table t (c1 int, ..., c10 int
> );
>
> -- there is a pk on say c1 column
> create materialized view mv as select c1, c2...c10 from
>
> On Sep 10, 2020, at 6:43 AM, Abraham, Danny wrote:
>
> Hi,
>
> We have seen several times a situation where a PKEY is compromised and
> duplicate values are created within a table.
>
> This has happened so far on PG 928 on Linux and Windows, and also on PG955 on
> AIX.
>
> We ran massiv
> On Aug 19, 2020, at 6:24 PM, David Rowley wrote:
>
> On Thu, 20 Aug 2020 at 09:55, Philip Semanchuk
> wrote:
>> I could use some help interpreting EXPLAIN ANALYZE output.
>>
>> -> Index Scan using ix_foo on t1 (cost=0.69..68.57 rows=3283 width=105)
>&
Hi all,
I could use some help interpreting EXPLAIN ANALYZE output.
-> Index Scan using ix_foo on t1 (cost=0.69..68.57 rows=3283 width=105)
(actual time=0.006..0.918 rows=3760 loops=94)
The actual rows returned by this plan node ~= 3760 * 94 = 353,440. Did postgres
expect (estimate) 3283 rows
> On Aug 11, 2020, at 8:01 PM, raf wrote:
>
> On Tue, Aug 11, 2020 at 06:38:39AM -0700, Miles Elam
> wrote:
>
>> Also of note: PostgreSQL already has a money type (
>> https://www.postgresql.org/docs/current/datatype-money.html)
>> But you shouldn't use it (
>> https://wiki.postgresql.org/w
> On Jul 25, 2020, at 8:21 AM, Pavel Stehule wrote:
>
>
>
> so 25. 7. 2020 v 14:04 odesílatel Scott Ribe
> napsal:
> > On Jul 24, 2020, at 9:55 PM, Pavel Stehule wrote:
> >
> > SELECT * FROM pg_config;
>
> That doesn't tell me whether or not it can actually be used.
>
> It shows if Pos
> On Jul 14, 2020, at 5:27 AM, Vishwa Kalyankar
> wrote:
>
> Hi,
>
> I have two machines - one with 8GB RAM & 4core CPU and the other with 64GB
> Ram & 24 core CPU. Both machines have the same DB (Postgres 12 + Postgis
> 2.5.3). Same query is taking less time in low end machine whereas
> On May 11, 2020, at 12:55 PM, Peter Devoy wrote:
>
> Hi list
>
> I need to store addresses for properties (as in real estate) so in my
> naivety I created a unique constraint like this:
>
> ALTER TABLE properties
>ADD CONSTRAINT is_unique_address
>UNIQUE (
>description, --e
48 matches
Mail list logo
