TicketQuery Wiki Macro

The TicketQuery macro lets you display ticket information anywhere that accepts WikiFormatting. The query language used by the [[TicketQuery]] macro is described in the TracQuery page.

Usage

[[TicketQuery]]

Wiki macro listing tickets that match certain criteria.

This macro accepts a comma-separated list of keyed parameters, in the form "key=value".

If the key is the name of a field, the value must use the syntax of a filter specifier as defined in TracQuery#QueryLanguage. Note that this is not the same as the simplified URL syntax used for query: links starting with a ? character. Commas (,) can be included in field values by escaping them with a backslash (\).

Groups of field constraints to be OR-ed together can be separated by a literal or argument.

In addition to filters, several other named parameters can be used to control how the results are presented. All of them are optional.

The format parameter determines how the list of tickets is presented:

  • list -- the default presentation is to list the ticket ID next to the summary, with each ticket on a separate line.
  • compact -- the tickets are presented as a comma-separated list of ticket IDs.
  • count -- only the count of matching tickets is displayed
  • rawcount -- only the count of matching tickets is displayed, not even with a link to the corresponding query (since 1.1.1)
  • table -- a view similar to the custom query view (but without the controls)
  • progress -- a view similar to the milestone progress bars

The max parameter can be used to limit the number of tickets shown (defaults to 0, i.e. no maximum).

The order parameter sets the field used for ordering tickets (defaults to id).

The desc parameter indicates whether the order of the tickets should be reversed (defaults to false).

The group parameter sets the field used for grouping tickets (defaults to not being set).

The groupdesc parameter indicates whether the natural display order of the groups should be reversed (defaults to false).

The verbose parameter can be set to a true value in order to get the description for the listed tickets. For table format only. deprecated in favor of the rows parameter

The rows parameter can be used to specify which field(s) should be viewed as a row, e.g. rows=description|summary

The col parameter can be used to specify which fields should be viewed as columns. For table format only.

For compatibility with Trac 0.10, if there's a last positional parameter given to the macro, it will be used to specify the format. Also, using "&" as a field separator still works (except for order) but is deprecated.

Examples

Example Result Macro
Number of Triage tickets: 644 [[TicketQuery(status=new&milestone=,count)]]
Number of new tickets: 644 [[TicketQuery(status=new,count)]]
Number of reopened tickets: 0 [[TicketQuery(status=reopened,count)]]
Number of assigned tickets: 413 [[TicketQuery(status=assigned,count)]]
Number of invalid tickets: 5213 [[TicketQuery(status=closed,resolution=invalid,count)]]
Number of worksforme tickets: 1073 [[TicketQuery(status=closed,resolution=worksforme,count)]]
Number of duplicate tickets: 4343 [[TicketQuery(status=closed,resolution=duplicate,count)]]
Number of wontfix tickets: 4156 [[TicketQuery(status=closed,resolution=wontfix,count)]]
Number of fixed tickets: 18662 [[TicketQuery(status=closed,resolution=fixed,count)]]
Number of untriaged tickets (milestone unset): 1057 [[TicketQuery(status!=closed,milestone=,count)]]
Total number of tickets: 35484 [[TicketQuery(count)]]
Number of tickets reported or owned by current user: 1488 [[TicketQuery(reporter=$USER,or,owner=$USER,count)]]
Number of tickets created this month: 46 [[TicketQuery(created=thismonth..,count)]]
Number of closed Firefox tickets: 8 [[TicketQuery(status=closed,keywords~=firefox,count)]]
Number of closed Opera tickets: 25 [[TicketQuery(status=closed,keywords~=opera,count)]]
Number of closed tickets affecting Firefox and Opera: 0 [[TicketQuery(status=closed,keywords~=firefox opera,count)]]
Number of closed tickets affecting Firefox or Opera: 33 [[TicketQuery(status=closed,keywords~=firefox|opera,count)]]
Number of tickets that affect Firefox or are closed and affect Opera: 33 [[TicketQuery(status=closed,keywords~=opera,or,keywords~=firefox,count)]]
Number of closed Firefox tickets that don't affect Opera: 0 [[TicketQuery(status=closed,keywords~=firefox -opera,count)]]
Last 3 modified tickets: #35529, #36264, #18448 [[TicketQuery(max=3,order=modified,desc=1,compact)]]

Details of ticket #1:

[[TicketQuery(id=1,col=id|owner|reporter,rows=summary,table)]]

Ticket Owner Reporter
#1 Jacob Adrian Holovaty
Summary Create architecture for anonymous sessions

Format: list

[[TicketQuery(version=0.6|0.7&resolution=duplicate)]]

This is displayed as:

No results

[[TicketQuery(id=123)]]

This is displayed as:

#123
Typo in the model_api/#field-types

Format: compact

[[TicketQuery(version=0.6|0.7&resolution=duplicate, compact)]]

This is displayed as:

No results

Format: count

[[TicketQuery(version=0.6|0.7&resolution=duplicate, count)]]

This is displayed as:

0

Format: progress

[[TicketQuery(milestone=0.12.8&group=type,format=progress)]]

This is displayed as:

Uncategorized

2008 / 2008

Bug

10199 / 10564

New feature

3729 / 4121

Cleanup/optimization

5276 / 5575

Format: table

You can choose the columns displayed in the table format (format=table) using col=<field>. You can specify multiple fields and the order they are displayed in by placing pipes (|) between the columns:

[[TicketQuery(max=3,status=closed,order=id,desc=1,format=table,col=resolution|summary|owner|reporter)]]

This is displayed as:

Full rows

In table format you can specify full rows using rows=<field>:

[[TicketQuery(max=3,status=closed,order=id,desc=1,format=table,col=resolution|summary|owner|reporter,rows=description)]]

This is displayed as:

Results (1 - 3 of 34427)

1 2 3 4 5 6 7 8 9 10 11
Ticket Resolution Summary Owner Reporter
#36263 invalid DB connection is not recreated after it closed due to time out. Sivakajan Sivaparan
Description

Issue Faced: When executing a query after a database connectivity loss (lasting more than 6 minutes), Django is unable to recreate a new connection automatically. This issue persists even when using connection.cursor(), saving a model instance (model.save()), or explicitly calling connection.ensure_connection().

However, if the connection is explicitly closed using connection.close(), Django successfully establishes a new connection to the database.

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/home/ubuntu/environment/db_tester/my_tester/views.py", line 20, in insert_model
    new_model.save()
  File "/home/ubuntu/.local/lib/python3.10/site-packages/django/db/models/base.py", line 892, in save
    self.save_base(
  File "/home/ubuntu/.local/lib/python3.10/site-packages/django/db/models/base.py", line 998, in save_base
    updated = self._save_table(
  File "/home/ubuntu/.local/lib/python3.10/site-packages/django/db/models/base.py", line 1161, in _save_table
    results = self._do_insert(
  File "/home/ubuntu/.local/lib/python3.10/site-packages/django/db/models/base.py", line 1202, in _do_insert
    return manager._insert(
  File "/home/ubuntu/.local/lib/python3.10/site-packages/django/db/models/manager.py", line 87, in manager_method
    return getattr(self.get_queryset(), name)(*args, **kwargs)
  File "/home/ubuntu/.local/lib/python3.10/site-packages/django/db/models/query.py", line 1847, in _insert
    return query.get_compiler(using=using).execute_sql(returning_fields)
  File "/home/ubuntu/.local/lib/python3.10/site-packages/django/db/models/sql/compiler.py", line 1836, in execute_sql
    cursor.execute(sql, params)
  File "/home/ubuntu/.local/lib/python3.10/site-packages/django/db/backends/utils.py", line 122, in execute
    return super().execute(sql, params)
  File "/home/ubuntu/.local/lib/python3.10/site-packages/django/db/backends/utils.py", line 79, in execute
    return self._execute_with_wrappers(
  File "/home/ubuntu/.local/lib/python3.10/site-packages/django/db/backends/utils.py", line 92, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/home/ubuntu/.local/lib/python3.10/site-packages/django/db/backends/utils.py", line 100, in _execute
    with self.db.wrap_database_errors:
  File "/home/ubuntu/.local/lib/python3.10/site-packages/django/db/utils.py", line 91, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/home/ubuntu/.local/lib/python3.10/site-packages/django/db/backends/utils.py", line 105, in _execute
    return self.cursor.execute(sql, params)
django.db.utils.OperationalError: server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.

[2025-03-18 11:34:35] ERROR my_tester.views: Database connection failed on 11:34:35
Traceback (most recent call last):
  File "/home/ubuntu/.local/lib/python3.10/site-packages/django/db/backends/base/base.py", line 298, in _cursor
    return self._prepare_cursor(self.create_cursor(name))
  File "/home/ubuntu/.local/lib/python3.10/site-packages/django/utils/asyncio.py", line 26, in inner
    return func(*args, **kwargs)
  File "/home/ubuntu/.local/lib/python3.10/site-packages/django/db/backends/postgresql/base.py", line 429, in create_cursor
    cursor = self.connection.cursor()
psycopg2.InterfaceError: connection already closed

Even if it says, connection already closed, I can not create a new healthy connection. If I call close() function and continue, it create a new connection form connection.cursor().

Recreate the Issue:

from django.db import connection
from django.db.utils import OperationalError
from my_tester.models import SimpleModel
import time
import logging

logger = logging.getLogger(__name__)

def insert_model(request):
    logger.info(f"======== Function triggered on:{time.strftime('%H:%M:%S')} ")
    model_name_suffix = int(round(time.time() * 1000))
    query = "INSERT INTO my_tester_simplemodel (model_name, model_description) VALUES (%s, %s);"
    
    for _ in range(200):
        try:
            check_connection(_)
            new_model = SimpleModel(model_name=f"model_{model_name_suffix}_{_}", model_description="model_description")
            new_model.save()
            check_connection(_)
            logger.info(f"Inserted: model_{model_name_suffix}_{_} on {time.strftime('%H:%M:%S')}")
            time.sleep(10)
        except Exception as e:
            logger.error(f"Database connection failed on {time.strftime('%H:%M:%S')}", exc_info=True)
            # connection.close()
            time.sleep(10)

    logger.info(f"+++++++++ Function end on:{time.strftime('%H:%M:%S')}")
    
    return "Models added successfully"
    
def check_connection(iterr):
    try:
        if connection.connection is not None:
            logger.info(f"Database connection is established {iterr} on {time.strftime('%H:%M:%S')}")
        else:
            logger.info(f"Database connection is not established {iterr} on {time.strftime('%H:%M:%S')}")
    except:
        logger.error(f"Connection checker {iterr} error on {time.strftime('%H:%M:%S')}")

In the above code set, I am sending a number of queries in a 10sec time interval. While the queries getting execute, I stop the connection between the Django app and the DB.( I created the application in the cloud9 instance and DB in the AWS RDS, both with the same security group. I can change the inbound rules of the SG and control the connectivity). When stop the connection, the app will wait for nearly 6mins( maybe the default idle session time). After that interval, it will throws the exception as I given above. It continuously throws the exception every 10 sec.

Then I enable the connection, still the django/ensure_connection is not creating a fresh connection at all.

[2025-03-18 08:28:59] INFO my_tester.views: ======== Function triggered on:08:28:59 
[2025-03-18 08:28:59] INFO my_tester.views: Database connection is not established 0 on 08:28:59
[2025-03-18 08:28:59] INFO my_tester.views: Database connection is established 0 on 08:28:59
[2025-03-18 08:28:59] INFO my_tester.views: Inserted: model_1742286539377_0 on 08:28:59
[2025-03-18 08:29:09] INFO my_tester.views: Database connection is established 1 on 08:29:09
[2025-03-18 08:29:09] INFO my_tester.views: Database connection is established 1 on 08:29:09
[2025-03-18 08:29:09] INFO my_tester.views: Inserted: model_1742286539377_1 on 08:29:09
[2025-03-18 08:29:19] INFO my_tester.views: Database connection is established 2 on 08:29:19
[2025-03-18 08:29:19] INFO my_tester.views: Database connection is established 2 on 08:29:19
[2025-03-18 08:35:09] ERROR my_tester.views: Database connection failed on 08:35:09
<Error Mentioned in the description>
[2025-03-18 08:35:19] INFO my_tester.views: Database connection is established 3 on 08:35:19
[2025-03-18 08:37:34] ERROR my_tester.views: Database connection failed on 08:37:34
<Error Mentioned in the description>
[2025-03-18 08:37:44] INFO my_tester.views: Database connection is established 4 on 08:37:44
[2025-03-18 08:39:58] ERROR my_tester.views: Database connection failed on 08:39:58
<Error Mentioned in the description>
[2025-03-18 08:40:08] INFO my_tester.views: Database connection is established 5 on 08:40:08
[2025-03-18 08:42:21] ERROR my_tester.views: Database connection failed on 08:42:21
<Error Mentioned in the description>
[2025-03-18 08:42:31] INFO my_tester.views: Database connection is established 6 on 08:42:31
[2025-03-18 08:44:44] ERROR my_tester.views: Database connection failed on 08:44:44
[2025-03-18 08:44:54] INFO my_tester.views: Database connection is established 7 on 08:44:54
[2025-03-18 08:47:08] ERROR my_tester.views: Database connection failed on 08:47:08

You can see, the connection.connection is not None in every iteration. In the middle of the execution, I reconnect the DB also. But no changes at all.

When I comment out the connection.close() in the code, then execute,

[2025-03-18 15:08:33] INFO my_tester.views: ======== Function triggered on:15:08:33 
[2025-03-18 15:08:33] INFO my_tester.views: Database connection is not established 0 on 15:08:33
[2025-03-18 15:08:33] INFO my_tester.views: Database connection is established 0 on 15:08:33
[2025-03-18 15:08:33] INFO my_tester.views: Inserted: model_1742310513542_0 on 15:08:33
[2025-03-18 15:08:43] INFO my_tester.views: Database connection is established 1 on 15:08:43
[2025-03-18 15:08:43] INFO my_tester.views: Database connection is established 1 on 15:08:43
[2025-03-18 15:08:43] INFO my_tester.views: Inserted: model_1742310513542_1 on 15:08:43
[2025-03-18 15:08:53] INFO my_tester.views: Database connection is established 2 on 15:08:53
[2025-03-18 15:14:43] ERROR my_tester.views: Database connection failed on 15:14:43
Traceback (most recent call last):
  File "/home/ubuntu/.local/lib/python3.10/site-packages/django/db/backends/utils.py", line 105, in _execute
    return self.cursor.execute(sql, params)
psycopg2.OperationalError: server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/home/ubuntu/environment/db_tester/my_tester/views.py", line 19, in insert_model
    new_model.save()
  File "/home/ubuntu/.local/lib/python3.10/site-packages/django/db/models/base.py", line 892, in save
    self.save_base(
  File "/home/ubuntu/.local/lib/python3.10/site-packages/django/db/models/base.py", line 998, in save_base
    updated = self._save_table(
  File "/home/ubuntu/.local/lib/python3.10/site-packages/django/db/models/base.py", line 1161, in _save_table
    results = self._do_insert(
  File "/home/ubuntu/.local/lib/python3.10/site-packages/django/db/models/base.py", line 1202, in _do_insert
    return manager._insert(
  File "/home/ubuntu/.local/lib/python3.10/site-packages/django/db/models/manager.py", line 87, in manager_method
    return getattr(self.get_queryset(), name)(*args, **kwargs)
  File "/home/ubuntu/.local/lib/python3.10/site-packages/django/db/models/query.py", line 1847, in _insert
    return query.get_compiler(using=using).execute_sql(returning_fields)
  File "/home/ubuntu/.local/lib/python3.10/site-packages/django/db/models/sql/compiler.py", line 1836, in execute_sql
    cursor.execute(sql, params)
  File "/home/ubuntu/.local/lib/python3.10/site-packages/django/db/backends/utils.py", line 122, in execute
    return super().execute(sql, params)
  File "/home/ubuntu/.local/lib/python3.10/site-packages/django/db/backends/utils.py", line 79, in execute
    return self._execute_with_wrappers(
  File "/home/ubuntu/.local/lib/python3.10/site-packages/django/db/backends/utils.py", line 92, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/home/ubuntu/.local/lib/python3.10/site-packages/django/db/backends/utils.py", line 100, in _execute
    with self.db.wrap_database_errors:
  File "/home/ubuntu/.local/lib/python3.10/site-packages/django/db/utils.py", line 91, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/home/ubuntu/.local/lib/python3.10/site-packages/django/db/backends/utils.py", line 105, in _execute
    return self.cursor.execute(sql, params)
django.db.utils.OperationalError: server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.

[2025-03-18 15:14:53] INFO my_tester.views: Database connection is not established 3 on 15:14:53
<Error as above>
[2025-03-18 15:17:17] INFO my_tester.views: Database connection is not established 4 on 15:17:17
<Error as above>
[2025-03-18 15:19:40] INFO my_tester.views: Database connection is not established 5 on 15:19:40
<Error as above>
[2025-03-18 15:22:04] INFO my_tester.views: Database connection is not established 6 on 15:22:04
[2025-03-18 15:22:23] INFO my_tester.views: Database connection is established 6 on 15:22:23
[2025-03-18 15:22:23] INFO my_tester.views: Inserted: model_1742310513542_6 on 15:22:23
[2025-03-18 15:22:33] INFO my_tester.views: Database connection is established 7 on 15:22:33
[2025-03-18 15:22:33] INFO my_tester.views: Database connection is established 7 on 15:22:33
[2025-03-18 15:22:33] INFO my_tester.views: Inserted: model_1742310513542_7 on 15:22:33

You can see in the command, it says Database connection is not established 6 on 15:22:04, which implies connection.connection is None that time.

Question: It says, the connection will be closed if the pipe between the DB and the application closed. But in this scenario, it seems the connection is not closed until we explicitly close the connection. Why that happen?

Also, please explain, can CONN_HEALTH_CHECK, CONN_MAX_AGE flags use to solve this type of issues. I tried with these, but no changes regarding of the CONN_HEALTH_CHECK value.(True/False)

#36261 wontfix `icontains` lookup doesn't work with case insensitive collations Craig de Stigter
Description

As recommended by django's warning messages when upgrading to 4.x we replaced our use of the CITEXT db type with a case insensitive collation (specifically und-u-ks-level2)

We have discovered that this prevents us from using search in the Django admin. The error we receive is

NotSupportedError: nondeterministic collations are not supported for LIKE 

This makes sense since the collation cannot supprot case-sensitive comparison. However, django admin's search box is supposed to be case-insensitive. It explicitly uses icontains.

It turns out that icontains is implemented using UPPER(fieldname) LIKE UPPER(pattern). Some of this logic is here (although I'm not sure where the second UPPER gets added)

How to fix it?

  1. It seems that icontains (and iexact etc) comparisons should use ILIKE, which would work fine in this situation. I have looked through git and ticket history and can't find any discussion of why it's implemented using LIKE instead.
  1. Alternatively, using x LIKE y COLLATE "default" seems to avoid the issue in this case, although someone who knows more about collations should probably weigh in on what other side-effects that might have...
#36258 invalid "error": "Model instances passed to related filters must be saved." Nabih Elshabrawi
Description

When I run this command

curl -X 'POST' \

'http://127.0.0.1:8000/api/dcim/devices/' \ -H 'accept: application/json' \ -H 'Content-Type: application/json' \ -H 'X-CSRFToken: 7Jn26w1vTrl1lYFF7sZLRgh5xclFfTDQNw7lQthQnocdjyHA1RZNNM6JPn2SrRcC' \ -d '{ "id": 81665, "display": "1-B DIR NAMERA NORTH 380KV, SHQ-2 380KV,AL LAITH 380KV", "name": "1-B DIR NAMERA NORTH 380KV, SHQ-2 380KV,AL LAITH 380KV", "device_type": 466, "device_role": 16, "tenant": 1, "platform": null, "serial": "2102300766N0J2000267", "asset_tag": null, "site": 784, "location": null, "rack": 21576, "position": 12, "face": "front", "parent_device": null, "status": "active", "airflow": null, "primary_ip": null, "primary_ip4": null, "primary_ip6": null, "cluster": null, "virtual_chassis": null, "vc_position": null, "vc_priority": null, "comments": "", "local_context_data": null, "custom_fields": {}

}'

I have this error "Model instances passed to related filters must be saved."

1 2 3 4 5 6 7 8 9 10 11


See also: TracQuery, TracTickets, TracReports

Last modified 14 months ago Last modified on Jan 24, 2024, 9:58:09 AM
Note: See TracWiki for help on using the wiki.
Back to Top