Plans for partitioning of inheriting tables
Hi Up to version 17, partitioning of tables inheriting from other tables is not possible. psql:../code_files/data_storage/PostgreSQL/tables/TOPO_FILES.pg_sql:68: ERROR: no se puede crear una tabla particionada como hija de herencia Are there plans to support this in the future? I could not find any hint in the documentation or in https://wiki.postgresql.org/wiki/Development_information. Kind regards Thiemo
Re: Plans for partitioning of inheriting tables
24.10.2024 22:58:39 David G. Johnston : > > My impression of things is that directly using “inherit” for table creation > is considered deprecated at this point. No one has interest in expanding on > the feature nor even recommends it be used in new development. That > particular unique feature of PostgreSQL hasn’t caught on. > > David J. > Thanks for sharing your experience. I wonder if this is the general take on inheritance for spreading common attributes throughout a database.
Re: Plans for partitioning of inheriting tables
Thanks for taking this up. 24.10.2024 22:44:11 Adrian Klaver : > > 1) Have you looked at?: > > https://www.postgresql.org/docs/current/ddl-partitioning.html#DDL-PARTITIONING-USING-INHERITANCE I do not feel it applies to my case. I tried to create a partitioned table that inherits columns from a base table. The documentation you provided the URL seems to speak of realising partitioning by using inheritance. > > 2) Provide the SQL you ran that got the above error? https://sourceforge.net/p/treintaytres/code/HEAD/tree/trunk/code_files/data_storage/PostgreSQL/tables/TEMPLATE_TECH.pg_sql
Re: CURRENTE_DATE
Hi,
I am just thinking, when DEFAULT CURRENT_DATE is being used in table
definition then why the function is again being used in INSERT statement
why not use
default. Here is sample
edb=# create table date_test (id int, hiredate date default current_date);
CREATE TABLE
edb=# insert into date_test values (1, *default*);
INSERT 0 1
edb=# select * from date_test;
id | hiredate
+
1 | 24-OCT-24 00:00:00
(1 row)
Regards,
Ikram
On Wed, Oct 23, 2024 at 6:18 PM Ray O'Donnell wrote:
> On 22/10/2024 12:31, Rossana Ocampos wrote:
>
> *Hello ,*
>
> I am new with PostgreSQL and I have a bug. I have created a function that
> has an input variable of type date , in case it does not receive value , it
> has to assume by default the current date.
>
> I have defined it as follows variable DATE DEFAULT CURRENT_DATE, but I
> get the following error.
>
>
>
> *El error *
>
> ERROR: invalid input syntax for type date: “CURRENT_DATE” LINE 1:
> ...extsupplydate ('1085018'::bigint, '5278'::bigint, 'CURRENT_D... ^ ERROR:
> invalid input syntax for type date: “CURRENT_DATE” SQL status: 22007
> Characters: 78
>
>
> I think you just need to leave off the quotes, as current_date is a
> function:
>
> insert into values ( ... , current_date, );
>
> Also, you don't need to quote the bigint values.
>
>
> HTH,
>
> Ray.
>
>
>
>
> Please could you help me, thank you very much.
>
> Rossana
>
>
>
> --
> Raymond O'Donnell // Galway // ireland...@rodonnell.ie
>
>
Re: Assistance Required: Timeout or Buffer Overflow Issue in PostgreSQL Client Application
Hi,
Please find the complete stack trace, the language is C and thread here was
just performing the operation of fetching records from the postgresDB using
SQL_get_tpf_rw() function for which I've also attached implementation here
as well
Stack trace of thread 966070:
#0
0xf7ee1129 __kernel_vsyscall (linux-gate.so.1)
#1
0xf6ba23b7 __poll (libc.so.6)
#2
0xf792e5b5 __interceptor_poll (libasan.so.8)
#3
0xf72b30a8 pqSocketCheck (libpq.so.5)
#4
0xf72b3864 pqWaitTimed (libpq.so.5)
#5
0xf72b38d2 pqWait (libpq.so.5)
#6
0xf72aff03 PQgetResult (libpq.so.5)
#7
0xf72b036a PQexecFinish (libpq.so.5)
#8
0x08106dd4 checkLOCK (server)
#9
0x0811d871 SQL_get_tpf_rw (server)
#10
0x08180269 get_tpf_rw (server)
#11
0x08189e47 FINDC (server)
#12
0x0818cd20 FINWC (server)
#13
0xf2d03778 _segKIP8 (kip8.so)
#14
0x0816ad0e call_segment (server)
#15
0x0816ba0b ENTRC (server)
#16
0xf2d096ee _segKIP5 (kip5.so)
#17
0x0816ad0e call_segment (server)
#18
0x0816ba0b ENTRC (server)
#19
0xf2d1722d _segKIP4 (kip4.so)
#20
0x0816ad0e call_segment (server)
#21
0x0816ba0b ENTRC (server)
#22
0xf2d1dee1 _segKIPB (kipb.so)
#23
0x0816ad0e call_segment (server)
#24
0x0816c454 ENTNC (server)
#25
0xf2d2be54 _segKIP9 (kip9.so)
#26
0x0816ad0e call_segment (server)
#27
0x081c264d call_appl_entry (server)
#28
0x0807f227 extest (server)
#29
0x0808923f handle_COMMS_TYPE_FUNCTIONAL_MESSAGE (server)
#30
0x0808d849 handle_comms_message (server)
#31
0xf77d6e41 worker (libuv.so.1)
#32
0xf78ff67a _ZL17asan_thread_startPv (libasan.so.8)
#33
0xf6b170ad start_thread (libc.so.6)
#34
0xf6bb1dea __clone (libc.so.6)
Regards,
Sasmit Utkarsh
+91-7674022625
On Thu, Oct 24, 2024 at 9:47 PM Igor Korot wrote:
> Hi,
> Is this a complete backtrace?
> Can you post a complete backtrace? We need to see if there is a reference
> to your application code?
>
> What language is it written in?
> What operation this thread was performing?
>
> Thank you.
>
> On Thu, Oct 24, 2024 at 11:12 AM Sasmit Utkarsh
> wrote:
> >
> > Dear PostgreSQL Community Team,
> >
> > I hope this message finds you well. I am reaching out for assistance
> with an issue encountered in our application, which communicates with
> PostgreSQL using the libpq client library.
> >
> > Issue Details:
> > We have observed a problem where one of the application's threads gets
> stuck during a database operation. Below is a stack trace of the affected
> thread:
> >
> > Application Logs:
> > Oct 23 10:08:44.806235 cucmtpccu1 shc-server@2.service[966034]:
> 0966070{ef5f81a7-d35b-4604-953d-a35665e505b7.01}KIP8-SQL_get
Assistance Required: Timeout or Buffer Overflow Issue in PostgreSQL Client Application
Dear PostgreSQL Community Team,
I hope this message finds you well. I am reaching out for assistance with
an issue encountered in our application, which communicates with PostgreSQL
using the libpq client library.
*Issue Details:*
We have observed a problem where one of the application's threads gets
stuck during a database operation. Below is a stack trace of the affected
thread:
*Application Logs:*
Oct 23 10:08:44.806235 cucmtpccu1 shc-server@2.service[966034]:
0966070{ef5f81a7-d35b-4604-953d-a35665e505b7.01}KIP8-SQL_get_tpf_rw()-SQL
read data from File Address before lock fa(-1810606079) fa(94145801) fa2
htonl(22549652)
Oct 23 10:08:44.806235 cucmtpccu1 shc-server@2.service[966034]:
0966070{ef5f81a7-d35b-4604-953d-a35665e505b7.01}KIP8-SQL_get_tpf_rw()
SelectDataCommand = CALL SQL_select_data_procedure($1, $2, NULL, NULL)
hold(0) fa(-1810606079)
Oct 23 10:08:44.807814 cucmtpccu1 shc-server@2.service[966034]: *** buffer
overflow detected ***: terminated
*Stack Trace of Thread 966070:*
#0 0xf7ee1129 __kernel_vsyscall (linux-gate.so.1)
#1 0xf6ba23b7 __poll (libc.so.6)
#2 0xf792e5b5 __interceptor_poll (libasan.so.8)
#3 0xf72b30a8 pqSocketCheck (libpq.so.5)
#4 0xf72b3864 pqWaitTimed (libpq.so.5)
#5 0xf72b38d2 pqWait (libpq.so.5)
#6 0xf72aff03 PQgetResult (libpq.so.5)
#7 0xf72b036a PQexecFinish (libpq.so.5)
#8 0x08106dd4 checkLOCK (server)
#9 0x0811d871 SQL_get_tpf_rw (server)
...
The stack trace shows that the thread is stuck in a poll operation while
waiting for socket activity within the PostgreSQL client library (libpq).
We suspect this could be related to a network timeout or issue. However,
the application logs indicate a buffer overflow before the crash, which
raises questions about whether these are related.
*Questions:*
-Could the buffer overflow be causing the crash, and if so, how is it
related to the socket activity?
-Are there specific configurations or checks we should perform to diagnose
this issue further?
-Any suggestions for possible solutions to resolve this problem?
For additional context, I've verified that the specified record does exist
in the database, and I am also attaching the implementation details for the
*checkLOCK* function corresponding to the stack trace.
Please let me know if you need any more details
Your assistance with troubleshooting this would be highly appreciated.
Regards,
Sasmit Utkarsh
+91-7674022625
void checkLOCK(int32_t fa)
{
int nFields;
int nTuples;
int i, j;
PGresult *checkLOCK_res=NULL;
char Command[100];
LOG_TRACE("%s() fa(%i)(%08X)",__func__,fa,fa);
snprintf(Command,sizeof(Command),"SELECT pid, classid, objid FROM
pg_locks WHERE objid=%i",fa);
//PQclear(checkLOCK_res);
checkLOCK_res = PQexec(conn,Command);
if (!checkLOCK_res) {
LOG_DEBUG("In %s(): PGresult is still NULL so return", __func__);
return;
}
if (PQresultStatus(checkLOCK_res) != PGRES_TUPLES_OK) {
LOG_DEBUG("checkLOCK failed: %s", PQerrorMessage(conn));
PQclear(checkLOCK_res);
return;
}
nFields = PQnfields(checkLOCK_res);
nTuples = PQntuples(checkLOCK_res);
if(nTuples > 0)
{
LOG_DEBUG("%s() fa(%i)(%08X) is currently LOCKED by PID %s as
classid=%s
objid=%s",__func__,fa,fa,PQgetvalue(checkLOCK_res,0,0),PQgetvalue(checkLOCK_res,0,1),PQgetvalue(checkLOCK_res,0,2));
PQclear(checkLOCK_res);
//printLOCKS();
}
else
{
LOG_DEBUG("%s() fa(%i)(%08X) is NOT LOCKED",__func__,fa,fa);
PQclear(checkLOCK_res);
}
}
Re: Assistance Required: Timeout or Buffer Overflow Issue in PostgreSQL Client Application
Hi,
Is this a complete backtrace?
Can you post a complete backtrace? We need to see if there is a reference
to your application code?
What language is it written in?
What operation this thread was performing?
Thank you.
On Thu, Oct 24, 2024 at 11:12 AM Sasmit Utkarsh wrote:
>
> Dear PostgreSQL Community Team,
>
> I hope this message finds you well. I am reaching out for assistance with an
> issue encountered in our application, which communicates with PostgreSQL
> using the libpq client library.
>
> Issue Details:
> We have observed a problem where one of the application's threads gets stuck
> during a database operation. Below is a stack trace of the affected thread:
>
> Application Logs:
> Oct 23 10:08:44.806235 cucmtpccu1 shc-server@2.service[966034]:
> 0966070{ef5f81a7-d35b-4604-953d-a35665e505b7.01}KIP8-SQL_get_tpf_rw()-SQL
> read data from File Address before lock fa(-1810606079) fa(94145801) fa2
> htonl(22549652)
> Oct 23 10:08:44.806235 cucmtpccu1 shc-server@2.service[966034]:
> 0966070{ef5f81a7-d35b-4604-953d-a35665e505b7.01}KIP8-SQL_get_tpf_rw()
> SelectDataCommand = CALL SQL_select_data_procedure($1, $2, NULL, NULL)
> hold(0) fa(-1810606079)
> Oct 23 10:08:44.807814 cucmtpccu1 shc-server@2.service[966034]: *** buffer
> overflow detected ***: terminated
>
> Stack Trace of Thread 966070:
> #0 0xf7ee1129 __kernel_vsyscall (linux-gate.so.1)
> #1 0xf6ba23b7 __poll (libc.so.6)
> #2 0xf792e5b5 __interceptor_poll (libasan.so.8)
> #3 0xf72b30a8 pqSocketCheck (libpq.so.5)
> #4 0xf72b3864 pqWaitTimed (libpq.so.5)
> #5 0xf72b38d2 pqWait (libpq.so.5)
> #6 0xf72aff03 PQgetResult (libpq.so.5)
> #7 0xf72b036a PQexecFinish (libpq.so.5)
> #8 0x08106dd4 checkLOCK (server)
> #9 0x0811d871 SQL_get_tpf_rw (server)
> ...
>
> The stack trace shows that the thread is stuck in a poll operation while
> waiting for socket activity within the PostgreSQL client library (libpq). We
> suspect this could be related to a network timeout or issue. However, the
> application logs indicate a buffer overflow before the crash, which raises
> questions about whether these are related.
>
> Questions:
> -Could the buffer overflow be causing the crash, and if so, how is it related
> to the socket activity?
> -Are there specific configurations or checks we should perform to diagnose
> this issue further?
> -Any suggestions for possible solutions to resolve this problem?
>
> For additional context, I've verified that the specified record does exist in
> the database, and I am also attaching the implementation details for the
> checkLOCK function corresponding to the stack trace.
>
> Please let me know if you need any more details
>
> Your assistance with troubleshooting this would be highly appreciated.
>
> Regards,
> Sasmit Utkarsh
> +91-7674022625
Re: Query performance issue
> > Additionally in the plan which mysql makes and showing the highest > response time, is it suffering because of differences of the speed of the > underlying IO/storage or is it just because of the optimization features > which are available in postgres and not there in mysql ? Trying to > understand if it can be identified from the execution plan itself. > I think trying to compare postgres and mysql plans against each other is not a very useful endeavor. There are fundamental design decisions between the two. Focus on making your Postgres query the best it can be, full stop. Optimize your queries, make sure the database is analyzed, and tweak some configs as needed. Also, you cannot accidentally forget a join condition. Yes, this is the primary reason. Cartesian joins hurt. Again , not able to clearly understand the third point you said below. Can > you please clarify a bit more. Do you mean we should write it as exists > /not exists rather IN and NOT IN and that will improve the performance? It gives Postgres more options on how to do things, so yes, it can be better. Cheers, Greg
Re: Plans for partitioning of inheriting tables
On Thursday, October 24, 2024, wrote: > > Up to version 17, partitioning of tables inheriting from other tables is > not possible. > >> psql:../code_files/data_storage/PostgreSQL/tables/TOPO_FILES.pg_sql:68: >> ERROR: no se puede crear una tabla particionada como hija de herencia >> > > Are there plans to support this in the future? I could not find any hint > in the documentation or in https://wiki.postgresql.org/wi > ki/Development_information. > My impression of things is that directly using “inherit” for table creation is considered deprecated at this point. No one has interest in expanding on the feature nor even recommends it be used in new development. That particular unique feature of PostgreSQL hasn’t caught on. David J.
